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

Question

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.

Ertuğrul Çetin · Accepted Answer · 2020-02-12 17:43:27Z

1

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'

edited Feb 12, 2020 at 17:43

Ertuğrul Çetin

5,2615 gold badges42 silver badges79 bronze badges

answered Feb 12, 2020 at 13:21

user330315

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related