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 — Harun Yilmaz
– Harun Yilmaz, Commented Jun 7, 2022 at 9:34

Bergi · Accepted Answer · 2022-06-07 11:15:21Z

1

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 ->->.

answered Jun 7, 2022 at 11:15

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jian · Accepted Answer · 2022-06-08 06:01:12Z

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 8, 2022 at 6:01

jian

5,0231 gold badge29 silver badges47 bronze badges

Collectives™ on Stack Overflow

Postgresql - querying jsonb throws a syntax error

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related