how to query array of nested json in postgresql

Question

i was scraping large and complex data and have problem in a column that has array of nested json. to simulate the issue: -

CREATE TABLE public.test
(
  id integer NOT NULL DEFAULT nextval('test_id_seq'::regclass),
  testval jsonb
)

sample data

INSERT INTO test (id, test) 
VALUES 
(111,
'[{"type": {"value": 0, "displayName": "test0"}, "value": "outertestvalue0"}, {"type": {"value": 1, "displayName": "test1"}, "value": "outertestvalue1"}]'
);
INSERT INTO test (id, test) 
VALUES 
(222,
'[{"type": {"value": 2, "displayName": "test2"}, "value": "outertestvalue2"}, {"type": {"value": 3, "displayName": "test3"}, "value": "outertestvalue3"}]'
);

question is how to filter out base on specific conditions

select * from test where testval->'type' ->>'displayName'='test1';

this didnt work. can anyone point me to right direction?

Answer 1

Use the JSON containment operator:

WHERE testval @> '[ { "type": { "displayName": "test1" } } ]'

This can be supported with a GIN index on the column.

Answer 2

If you want to use a wildcard search (e.g. LIKE) you need to unnest the array:

select t.*
from test t
where exists (select *
              from jsonb_array_elements(t.testval) as a(x)
              where a.x -> 'type' ->> 'displayName' like 'foo%');

With Postgres 12 this can be written a bit simpler using the new jsonb_path_exists() function:

select *
from test
where jsonb_path_exists(testval, '$.type.displayName ? (@ starts with "foo")');

Online example