Postgres @> operator confusion

Question

I seem to be having issues with the @> operator:

CREATE TEMP TABLE test_jsonb (id SERIAL, data jsonb);
INSERT INTO test_jsonb (data) 
VALUES ('[{"role_id": "1", "user_name": "borrey"}]');
CREATE TABLE
INSERT 0 1
SELECT data::text, jsonb_typeof(data), jsonb_array_length(data),
 (data->0)::text,
 data @> (data->0) AS contains_self,
 data @> '{"user_name":"borrey","role_id":"1"}'::jsonb AS contains_manual
FROM test_jsonb;
 
 data | jsonb_typeof | jsonb_array_length | text | contains_self | contains_manual 
-------------------------------------------+--------------+--------------------+-----------------------------------------+---------------+-----------------
 [{"role_id": "1", "user_name": "borrey"}] | array | 1 | {"role_id": "1", "user_name": "borrey"} | f | f
(1 row)

What am I doing wrong?

I would expect contains_self and contains_manual to return t but it is not.

select version();
 version 
---------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.4 (Debian 17.4-1.pgdg120+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

• user7070613: Read 8.14.3. jsonb Containment and Existence and you will see why your assumptions are wrong.

Answer 1

When using the contains operator with an array, the right hand side of the operator needs to be an array as well. You can try:

data @> jsonb_build_array(data->0)

Answer 2

If you are going to do this sort of searching of jsonb data for contained elements it would be a good idea to learn the SQL/JSON Path Language as described here:

https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-PATH

Then to solve your issue you would use something like:

select 
 jsonb_path_query('[{"role_id": "1", "user_name": "borrey"}]'::jsonb, '$[*]') 
@> 
 '{"role_id": "1", "user_name": "borrey"}'::jsonb;
t

In the above $ represents the item being queried and $[*] fetches the elements in the array, in this case a single object. Then the object is compared to the desired object with @>. For this particular case it a bit of overkill, but if you start digging deeper into jsonb structures it becomes more important.

• Bergi: I don't think you would ever mix jsonb_path_query with @>. Rather you would just write data @? '$[*] ? (@.role_id == 1 && @.user_name == "borrey")'
• user7070613: Except: select '[{"role_id": "1", "user_name": "borrey"}]'::jsonb @? '$[*] ? (@.role_id == 1 && @.user_name == "borrey")' returns f.
• Bergi: Oh, it's "1" not 1.