How easy is it to choose an "activity"?
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 object '{"name": "Bob", "occupation": "janitor"}'
How do I return Bob's profession ("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
Are looking for:
occupation
----------
janitor
If you care about the string where jsonb is located, you can output all elements from 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 line)
source to share
Your "people" is an array. You can get the elements of an array using a function jsonb_array_elements
. After that, you can simply filter by person->>'name'
:
SELECT person->>'occupation' as occupation
FROM (
SELECT person.value as person
FROM (
SELECT
'{"people":
[
{"name": "Bob", "occupation": "janitor"},
{"name": "Susan", "occupation": "CEO"}
]
}'::jsonb as data
) a
CROSS JOIN
jsonb_array_elements(data->'people') as person
) b
WHERE person->>'name' = 'Bob';
Note that it ->>
returns text and ->
returns jsonb
.
source to share