How do I just select the "occupation"?

Question

I have the following:

SELECT * 
FROM (
   SELECT '{"people": [{"name": "Bob", "occupation": "janitor"}, {"name": "Susan", "occupation": "CEO"}]}'::jsonb as data
) as b 
WHERE data->'people' @> '[{"name":"Bob"}]'::jsonb;

I am filtering for the object '{"name": "Bob", "occupation": "janitor"}'

How do I return Bob's occupation ("janitor")?

SELECT data->'people'->>'occupation'  
FROM (
   SELECT '{"people": [{"name": "Bob", "occupation": "janitor"}, {"name": "Susan", "occupation": "CEO"}]}'::jsonb as data
) as b 
WHERE data->'people' @> '[{"name":"Bob"}]'::jsonb;

returns

?column?
--------
NULL

Looking for:

occupation
----------
janitor

Answer 1

If you don't care about anything else on the row where the jsonb is, you can take all elements out of the jsonb and then use them as separate elements to select from

SELECT data->>'occupation' as occupation
FROM (
  SELECT jsonb_array_elements(
    '{"people": 
       [
         {"name": "Bob", "occupation": "janitor"}, 
         {"name": "Susan", "occupation": "CEO"}
       ]
     }'::jsonb->'people') as data) as b
WHERE data @> '{"name":"Bob"}';

Results

occupation
-----------
janitor
(1 row)