[SOLVED] Postgres @> operator confusion

Postgres @> operator confusion

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)

Solution

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)