0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.