I have a table with following structure (simplified):
Sometable:
id | data |
---|---|
1 | {"data": [{"type": {"code": "S"}}, {"type": {"code": "aB"}}]} |
2 | {"data": [{"type": {"code": "B"}}]} |
‘Data’ is jsonb type, json structure is always the same. I need to find all records where ‘code equals certain value, for example ‘B’.
I’ve tried:
select * from sometable t where 'B' in (jsonb_array_elements((t.data->'data'))#>>'{type, code}');
But that gives me an error:
set-returning functions are not allowed in WHERE.
Basically, anything I’ve tried in ‘where’ with ‘jsonb_array_elements’ gives that error. Is there any other way to find records by value of the ‘code’ key?
Advertisement
Answer
You can use the @>
operator
select * from sometable t where (t.data -> 'data') @> '[{"type": {"code": "B"}}]'
or
select * from sometable t where t.data @> '{"data": [{"type": {"code": "B"}}]}'