Postgresql - querying jsonb throws a syntax error

Question

I have a table that has a column data of jsonb type.

create table event
(
    id             bigserial
        primary key,
    created_at     timestamp with time zone default now() not null,
    type           text                                   not null,
    created_by     text,
    data           jsonb,
    event_time     timestamp with time zone default now() not null
);

In that field I am saving a json object that looks like this:

{
  "comment": "Changed by recipient",
  "source": "Recipient page"
}

I would like to query values in that table by the value of the comment property of the data json object. Something like this in based by examples [here][1]:

select * from event
where type = 'pickup-data-changed' 
  and data -> 'comment' = 'Changed by recipient'

If I query like that I get an invalid token error:

[22P02] ERROR: invalid input syntax for type json Detail: Token "Changed" is invalid. Position: 104

What am I doing wrong here? If I do it as a double arrow like suggested in the comments:

select * from event
    where type = 'pickup-data-changed' 
      and data ->-> 'comment' = 'Changed by recipient'

I get an error:

[42883] ERROR: operator does not exist: jsonb ->-> unknown Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

How can I make this query work? [1]: https://kb.objectrocket.com/postgresql/how-to-query-a-postgres-jsonb-column-1433

Does this answer your question? [Postgresql query for objects in nested JSONB field](https://stackoverflow.com/questions/42130740/postgresql-query-for-objects-in-nested-jsonb-field) — Harun Yilmaz, Jun 07 '22 at 09:34

score 1 · Accepted Answer · answered Jun 07 '22 at 11:15

I get an invalid token error. What am I doing wrong here?

data -> 'comment' returns a value of type jsonb, so the right hand side of the comparison 'Changed by recipient' is parsed as JSON as well - and it's invalid JSON. To create a JSON string value to compare against, you'd need to write

… data -> 'comment' = '"Changed by recipient"'

If I do it as a double arrow like suggested in the comments, data ->-> 'comment'

The comments suggested

… data ->> 'comment' = 'Changed by recipient'

not ->->.

score 0 · Answer 2 · answered Jun 08 '22 at 06:01

0

alternatives:

select * from event
where type = 'pickup-data-changed'
  and data -> 'comment' = '"Changed by recipient"'::jsonb;

or

select * from event
where type = 'pickup-data-changed'
  and data['comment'] = '"Changed by recipient"'::jsonb;

answered Jun 08 '22 at 06:01

jian

4,119
1
17
32

Postgresql - querying jsonb throws a syntax error

2 Answers2