what should I do q jsonb query in postgreSQL 13

Question

I have stored jsonb data in PostgreSQL 13 like this:

[{"code": "OFFICIAL"}, {"code": "FULLTEXT"}]

how to query the jsonb data type? I have tried like this:

select count(*) from test where tags::jsonb ? 'code';
select count(*) from test where tags::jsonb ->> 'code' = 'OFFICIAL';
select count(*) from test where tags::jsonb @> '{"code": "OFFICIAL"}';

both could not work. this is the table DDL:

CREATE TABLE public.test (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    tags jsonb NULL,
    CONSTRAINT test_pkey PRIMARY KEY (id)
);

what should I do to make it work? Also tried like this:

select * from (
    select jsonb_array_elements(tags) as tt from test
) a
where  tt -> 'code' = 'OFFICIAL'

Answer 1

It's an array of objects, so you need to provide an array for e.g. the @> operator:

select count(*) 
from test 
where tags @> '[{"code": "OFFICIAL"}]'

Or you can use a JSON path expression:

select count(*) 
from test 
where tags @@ '$[*].code == "OFFICIAL"'