Search code examples
jsonpostgresqljsonb

How to only keep properties from a Postgres jsonb object that exist in an array of keys?

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?

Solution

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