Skip to content
Advertisement

Search by json key in jsonb column PostgreSQL

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"}}]}'

Online example

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement