clear markdown compare help donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 497160 fiddles created (9388 in the last week).

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"}]}');
3 rows affected
 hidden batch(es)


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"}');
id data
1 {"my_arr": [{"serial": "AAA", "content": "123"}, {"serial": "BBB", "content": "345"}], "test_row": 1}
3 {"my_arr": [{"serial": "AAA", "content": "678"}, {"serial": "EEE", "content": "789"}], "test_row": 3}
 hidden batch(es)


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');
id data
1 {"my_arr": [{"serial": "AAA", "content": "123"}, {"serial": "BBB", "content": "345"}], "test_row": 1}
3 {"my_arr": [{"serial": "AAA", "content": "678"}, {"serial": "EEE", "content": "789"}], "test_row": 3}
 hidden batch(es)