Re: retrieve subset of a jsonb object with a list of keys - Mailing list pgsql-general

On 19/08/15 13:37, Tom Smith wrote:
> Hi:
>
> I have a jsonb columne with json object like belo
> {"a": 1, "b":2, "c":3}
>
> I'd like to get subset of the object with key list ["a","c"]
> so it retruns json object of
>
> {"a": 1,  "c":3}
>
> something like
>
> select '{"a": 1, "b":2, "c":3}'::jsob ->'["a","c"]'
>
> what would be the most efficient (and simplest if possible) to get the
> subset with the key list?

Hi,

I came up with this:

select json_object_agg(key, value)
  from jsonb_each_text('{"a": 1, "b":2, "c":3}'::jsonb)
  where key in ('a', 'c');

IDK if there is a shorter way. You might want to wrap this in a function.

Bye,
Chris.