Search by json key in jsonb column PostgreSQL

Question

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?

score 1 · Accepted Answer · 2021-07-19 15:52:46Z

1

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

edited Jul 19, 2021 at 15:52

answered Jul 19, 2021 at 14:22

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

SergeiTonoian Over a year ago

I've tried that, unfortunately doesn't work

user330315 Over a year ago

@SergeiTonoian: sorry, there was a typo. See my edit

Collectives™ on Stack Overflow

Search by json key in jsonb column PostgreSQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related