Select key from json inside array in POSTGRES [duplicate]

Question

I have a jsonb column called props that looks like this:

{"house_color": "white", "dynamic_properties": [{ "visibility" : "blue" }, { "autonomy" : "self"}]}

Right now I know how to get the all the colors for all of my houses by doing:

select "props"->"house_color" as color FROM houses

Now, I'm trying to get all the visibility dynamic properties by doing some type of condition in the SELECT, and I'm stuck. So, in the example I would just get 'blue'. I'm looking for something like

select "props"->"dynamic_properties"->"visibility" as color FROM houses

But I don't know how to look in within objects in that dynamic_properties array.

S-Man · Accepted Answer · 2018-12-19 08:26:57Z

0

demo:db<>fiddle

WITH jsondata AS (
    SELECT '{"house_color": "white", "dynamic_properties": [{ "visibility" : "blue" }, { "autonomy" : "self"}]}'::json as data
)
SELECT
    data ->> 'house_color'
FROM jsondata, json_array_elements(data -> 'dynamic_properties') as elem
WHERE elem ->> 'visibility' = 'blue'

json_array_elements expands the array into one row per element. So in your example the result are two rows. These rows can be filtered by its key/value pair.

answered Dec 19, 2018 at 8:26

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

Select key from json inside array in POSTGRES [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related