Postgres select item from json in json

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 -->

CodePudding user response：

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.