Postgres select item from json in json_agg

Question

with a as(
SELECT '[{"residential" : 100}, {"childcare" : 200}, {"open space" : 50}]'::jsonb t
)
select t->'childcare' from a

this keeps returning null when it should give me back 200.

same thing if I do -->

GMB · Accepted Answer · 2022-12-06 21:14:20Z

1

Your data is an array of jsonb objects; you want to search the array for the object that has key childcare, and return the associated value.

For this, you would typically use a lateral join and jsonb_array_elements to unnest the array, then filter the resulting objects with operator ?.

So:

select x ->> 'childcare' childcare
from a
inner join lateral jsonb_array_elements(a.t) as x(elt) on x.elt ? 'childcare'

Note that ->> should be used instead of -> in the select clause : the former gives you back a text value, while the latter returns jsonb.

answered Dec 6, 2022 at 21:14

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Answer 1