query an indexed jsonb column

Question

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)

Answer 1

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.