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 -->
https://www.postgresql.org/docs/current/functions-json.html
>Solution :
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.