Postgres select specific nested keys from json column

Question

Have a table with json column, this json is quite big, so I want to filter/select only speicific nested keys.

Json Example:

{
   "title":{
      "nested_1":"This is nested key 1",
      "nested_2":"This is nested key 2",
      "nested_3":"This is nested key 3",
      "nested_4":"This is nested key 4",
      "nested_5":"This is nested key 5"
   },
   "description":{
      "nested_1":"This is nested key 1",
      "nested_2":"This is nested key 2",
      "nested_3":"This is nested key 3",
      "nested_4":"This is nested key 4",
      "nested_5":"This is nested key 5"
   },
   "meta":{
      "nested_1":"This is nested key 1",
      "nested_2":"This is nested key 2",
      "nested_3":"This is nested key 3",
      "nested_4":"This is nested key 4",
      "nested_5":"This is nested key 5"
   }
}

In example I want to select only nested_3 and nested_5 (But keep the json structure):

{
   "title":{
      "nested_3":"This is nested key 3",
      "nested_5":"This is nested key 5"
   },
   "description":{
      "nested_3":"This is nested key 3",
      "nested_5":"This is nested key 5"
   },
   "meta":{
      "nested_3":"This is nested key 3",
      "nested_5":"This is nested key 5"
   }
}

What I tried so far:

select 
id,
(select json_object_agg(key, val) from (
    values 
    ('nested_3', (select json_object_agg(t.token, t.content->'nested_3') from json_each(json_col) as t(token, content))),
    ('nested_5', (select json_object_agg(t.token, t.content->'nested_5') from json_each(json_col) as t(token, content)))
)  as foo(key, val)) as json_col
from my_table

This is working but gives the opposite result (logical, based on above query):

{
   "nested_3": {
      "title": "This is nested key 3",
      "description": "This is nested key 3",
      "meta": "This is nested key 3"
   },
   "nested_5": {
      "title": "This is nested key 5",
      "description": "This is nested key 5",
      "meta": "This is nested key 5"
   }
}

I've also tried to nest:

(select json_object_agg(t.token, json_object_agg(t.content->'nested_3', t.languages->'nested_5')) from json_each(json_col) as t(token, content))

But this gives me an error: aggregate function calls cannot be nested

There is a way to select only specific nested keys but preserve the json structure?

Postgres version: 12

Stefanov.sm · Accepted Answer · 2021-08-28 13:23:36Z

2

With a declared instead of hard-coded list of keys, in ('nested_3', 'nested_5').

select id, (
 select jsonb_object_agg(key, (
    select jsonb_object_agg(key, value) 
    from jsonb_each(t.value) 
    where key in ('nested_3', 'nested_5')
 )) json_filtered
 from jsonb_each(jsonfield) t
) from the_table;

SQL Fiddle

edited Aug 28, 2021 at 13:23

answered Aug 28, 2021 at 13:12

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SergkeiM Over a year ago

Hi @Stefanov.sm, can you explain a little bit the query and point the difference between other solution?

Stefanov.sm Over a year ago

@Froxz Instead of json_build_object('nested_3', t.content->'nested_3', 'nested_5', t.content->'nested_5') I used select jsonb_object_agg(key, value) from jsonb_each(t.value) where key in ('nested_3', 'nested_5'). The only difference is that the list of filtered fields is in a bit more declarative form, can be changed, grown and shrunk w/o touching other parts of the code.

SergkeiM · Accepted Answer · 2021-08-28 07:29:02Z

1

Found a way to do this: by using json_build_object

So what happens in json_each on each loop we create new object with only needed keys/value, and after push it to our output using json_object_agg if the key already exists will push to previous object otherwise will create new key.

select 
id,
(
   select 
   json_object_agg(t.token, json_build_object('nested_3', t.content->'nested_3', 'nested_5', t.content->'nested_5'))
   from json_each(json_col) as t(token, content)
) as json_col
from my_table

If someone has better solution or maybe better description explanation, please post your answer.

answered Aug 28, 2021 at 7:29

SergkeiM

4,1886 gold badges40 silver badges73 bronze badges

Collectives™ on Stack Overflow

Postgres select specific nested keys from json column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related