PostgreSQL: Find row with JSON object attribute in array

Question

I have a postgres database which is set up like this:

CREATE TABLE arr_test (
  id serial primary key,
  data jsonb
);

INSERT INTO arr_test (data) VALUES 
  ('{"test_row": 1, "my_arr": [{"serial":"AAA", "content":"123"}, {"serial":"BBB", "content":"345"}]}'),
  ('{"test_row": 2, "my_arr": [{"serial":"CCC", "content":"456"}, {"serial":"DDD", "content":"567"}]}'),
  ('{"test_row": 3, "my_arr": [{"serial":"AAA", "content":"678"}, {"serial":"EEE", "content":"789"}]}');

I am trying to write a query where the data contains a list which has an object which contains a value. In this case it is the serial-field of the objects in the array that I want to query for.
For example, if I query for serial 'AAA' I want test row 1 and 3 to be returned, and if I query for serial 'EEE' I want only test row 3.

I have experimented with jsonb_array_elements and jsonb_to_recordset but am getting nowhere and would appreciate any help.

Answer 1

You need to unnest the array elements and combine that with an EXISTS condition

select t.*
from arr_test t
where exists (select *
              from jsonb_array_elements(t.data -> 'my_arr') as x(o)
              where x.o ->> 'serial' = 'AAA');

Alternatively you can write that with a contains operator @>:

select t.*
from arr_test t
where exists (select *
              from jsonb_array_elements(t.data -> 'my_arr') as x(o)
              where x.o @> '{"serial": "AAA"}');

Try it on dbfiddle