I want to query a jsonb column with the @> operator.

This query gives results:

select * from json_table1
where p_attribute @> '{"age":"37"}'

This query doesn't fail but also doesn't give results:

select * from json_table1
where p_attribute -> 'age' @> '37'::jsonb

The table used in the example:

create table json_table1 (
    p_id int primary key,
    first_name varchar(20),
    last_name varchar(20),
    p_attribute jsonb,
    quote_content text

The index on p_attribute:

create index gin_idx on json_table1 using gin(p_attribute jsonb_path_ops)

Since "37" is a string, not a number, your second query would have to use a jsonb string:

... WHERE p_attribute -> 'age' @> '"37"'::jsonb

Note the double quotes around 37.

Also note that this query wouldn't be able to use the index, while the first query can.

| improve this answer | |
  • Why doesn't the second query use the index? Is there a way I can index keys and values seperately so I can text search like I would with to_tsquery? – saltiest potato Jun 4 at 13:50
  • Yes, then you have to add a regular B-tree index on json_table1 ((p_attribute -> 'age')). Obviously such an index can only be used for WHERE conditions on that expression. – Laurenz Albe Jun 4 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.