Use JSONpath to re-create existing objects with a subset of the original keys-CodePudding

Suppose I have an object like this:

{
   "foo": "bar",
   "baz": "quux",
   "erase": "me",
   "superfluous": true
}

Is it possible to construct a JSONpath query that returns a new object with only a subset of the original keys, e.g.,

{
   "foo": "bar",
   "baz": "quux",
}

The reason for this "filtering" is that I have a json attribute as part of an entity in PostgreSQL that usually contains a list of complex JSON objects. Retrieving all the data gives me approx 5MB for the attribute alone (per result row), while for that specific query, I need only two attributes of the JSON object.

Would this be possile with a json_path_query() alone, or would I need a different approach?

CodePudding user response：

There is no built-in function, but it's easy to write one:

create function keep_keys(p_input jsonb, p_to_keep text[])
  returns jsonb
as
$$
  select jsonb_object_agg(key, value
  from jsonb_each(p_input) as t(key, value)
  where t.key = any (p_to_keep);
$$
language sql;

This:

select keep_keys('{"foo": "bar",
                   "baz": "quux",
                   "erase": "me",
                   "superfluous": true}', 
                 array['foo', 'baz']);

Returns: {"baz": "quux", "foo": "bar"}

CodePudding user response：

I suppose you need something like this:

SELECT json_build_object('foo', json_field->>'foo','baz', json_field->>'baz') as short_json_field FROM your_table;