I have a jsonb object with numerous properties, and I have a Postgres array of keys that I want to extract from the object, into a new, stripped-down object.
If my object is:
'{"foo": true, "bar": 2, "baz": "cat", "other": "Some text"}'::jsonb
and my array of properties to extract is '{foo,other}', my desired result is:
'{"foo": true, "other": "Some text"}'::jsonb
How can I achieve this?
Borrowing from this answer...
select jsonb_object_agg(key,value)
from jsonb_each('{"foo": true, "bar": 2, "baz": "cat", "other": "Some text"}'::jsonb)
where key = any('{foo,other}')
jsonb_each turns the JSON into a table of key (text) and value (jsonb) columns which can then be queried normally.
where key = any('{foo,other}') is basically where key in ('foo', 'other') but for arrays.
Finally jsonb_object_agg(key,value) aggregates all the matched rows into one JSON object.
You could do it like this:
SELECT jsonb_object_agg(elem.key, elem.val)
FROM (jsonb_each(
JSONB '{"foo": true, "bar": 2, "baz": "cat", "other": "Some text"}'
) AS elem(key, val)
JOIN (unnest(
TEXT[] '{foo,other}'
) AS filter(key) USING (key);
