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.