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.

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'

Advertisement

Answer