Skip to content
Advertisement

How to get specific objects value from JSONB array in PostgreSQL?

I have a column named people and it’s type is JSONB.

Here is the sample data (1 row):

{"addresses": [{"street":"cubuklu", "valid?": "true"} 
               {"street":"beykoz", "valid?":"false"}
               {"street":"kavacik", "valid?": "true"} ]}

I would like to get list of streets that have valid? true value for all rows.

Result:

cubuklu
kavacik
......(data from other rows)

I’m able to list of arrays could not filter values tho.

Advertisement

Answer

You need to unnest the array and then filter on the result:

select adr.address ->> 'street'
from the_table t
  cross join jsonb_array_elements(t.people -> 'addresses') as adr(address)
where adr.address ->> 'valid?' = 'true'
6 People found this is helpful
Advertisement