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.

2 Answers 2

3

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

1

This can also work

select
t.id,
jsonb_path_query(data, '$ ? (@.my_arr[*].serial == "AAA")')
from arr_test t

From official docs: JSON Functions and Operators

jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent boolean ]] ) → setof jsonb

Returns all JSON items returned by the JSON path for the specified JSON value. The optional vars and silent arguments act the same as for jsonb_path_exists.

select * from jsonb_path_query('{"a":[1,2,3,4,5]}', $.a[*] ? (@ >= $min && @ <= $max)', 

'{"min":2, "max":4}') →

 jsonb_path_query
------------------
 2
 3
 4

Regarding performance comparison to the previous answer, I do not know. Index awareness in jsonb functions was not on the top but it is getting better with every Postgres release.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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