Hierarchically aggregate JSON depending on value in row using PostgreSQL10

Question

I have a PostgreSQL 10 table that works as a "dictionary" and is structured as follows:

key	value
style_selection_color
style_selection_weight
style_line_color
style_line_weight
...

Now I was wondering if there is a way of building a JSON with the values in the table where it would build a hierarchy depending on the value of "key"? Something like:

style --> selection --> color and style --> line --> color

Ending up with a JSON:

{
  style: [
    selection: {
      color: "...",
      weight: "..."
    },
    line: {
      color: "...",
      weight: "..."
    }
  ]
}

Is such a feat achievable? If so, how would I go about it?

Could it be done so that regardless of what keys I have in my table it always returns the JSON properly built?

Thanks in advance

https://stackoverflow.com/q/42222968/125032 you can use split_part function on key to split it to multiple parts. — hazimdikenli, Jan 28 '22 at 13:38

Edouard · Accepted Answer · 2022-01-28T16:18:12.537

Working solution with PosGres 10 and above

I propose you a generic solution which convert the key data into text[] type so that it can be used as jsonpath inside the standard jsonb_set() function.

But as we will iterate on the jsonb_set() function, we need first to create an aggregate function associated to that function :

CREATE AGGREGATE jsonb_set_agg(p text[], z jsonb, b boolean)
( sfunc = jsonb_set
, stype = jsonb
, initcond = '{}'
)

Then we convert the key data into text[] and we automatically generate the list of jsonpath that will allow to build progressively and iteratively the final jsonb data :

 SELECT i.id
      , max(i.id) OVER (PARTITION BY t.key) AS id_max
      , p.path[1 : i.id] AS jsonbpath
      , to_jsonb(t.value) AS value
   FROM mytable AS t
  CROSS JOIN LATERAL string_to_array(t.key, '_') AS p(path)
  CROSS JOIN LATERAL generate_series(1, array_length(p.path, 1)) AS i(id)

The final query looks like this :

WITH list AS
( SELECT i.id
       , max(i.id) OVER (PARTITION BY t.key) AS id_max
       , p.path[1 : i.id] AS jsonpath
       , to_jsonb(t.value) AS value
   FROM mytable AS t
  CROSS JOIN LATERAL string_to_array(t.key, '_') AS p(path)
  CROSS JOIN LATERAL generate_series(1, array_length(p.path, 1)) AS i(id)
)
SELECT jsonb_set_agg( l.jsonpath
                    , CASE 
                        WHEN l.id = l.id_max THEN l.value
                        ELSE '{}' :: jsonb
                      END
                    , true 
                    ORDER BY l.id
                    )
  FROM list AS l

And the result is slightly different from your expectation (the top-level json array is replaced by a json object) but it sounds like more logic to me :

{"style": {"line": {"color": "C"
                   , "weight": "D"
                   }
          , "selection": {"color": "A"
                         , "weight": "B"
                         }
          }
}

full test result in dbfiddle.

This is genius. Thank you very much! This is exactly what I needed! Apologies for the late reply btw. — Asfourhundred, Mar 30 '22 at 11:36

hazimdikenli · Answer 2 · 2022-01-28T15:52:20.123

Well, I am not sure about Postgres version, hoping this would work on your version, I tried this on version 11.

        ;WITH dtbl as (
    select split_part(tbl.col, '_', 1) as style,
           split_part(tbl.col, '_', 2) as cls,
           split_part(tbl.col, '_', 3) as property_name,
           tbl.val
    from (
             select 'style_selection_color' as col, 'red' as val
             union all
             select 'style_selection_weight', '1rem'
             union all
             select 'style_line_color', 'gray'
             union all
             select 'style_line_weight', '200'
             union all
             select 'stil_line_weight', '200'
         ) as tbl
),
     classes as (
         select dtbl.style,
                dtbl.cls,

                (
                    SELECT json_object_agg(
                                   nested_props.property_name, nested_props.val
                               )
                    FROM (
                             SELECT dtbl2.property_name,
                                    dtbl2.val
                             FROM dtbl dtbl2
                             where dtbl2.style = dtbl.style
                               and dtbl2.cls = dtbl.cls
                         ) AS nested_props
                ) AS properties

         from dtbl
         group by dtbl.style, dtbl.cls),
     styles as (
         select style
         from dtbl
         group by style
     )
        ,
     class_obj as (
         select classes.style,
                classes.cls,
                json_build_object(
                        classes.cls, classes.properties) as cls_json
         from styles
                  join classes on classes.style = styles.style
     )
select json_build_object(
               class_obj.style,
               json_agg(class_obj.cls_json)
           )
from class_obj
group by style
    ;

If you change the first part of the query to match your table and column names this should work. The idea is to build the json objects nested, but you cannot do this on one pass, as it does not let you nest json_agg functions, this is why we have to use more than 1 query. first build line and selection objects then aggregate them in the style objects. Sorry for the naming, this is the best I could do.

EDIT1: This is the output of that query.

"{""stil"" : [{""line"" : [{""weight"" : ""200""}]}]}"
"{""style"" : [{""selection"" : [{""color"" : ""red""}, {""weight"" : ""1rem""}]}, {""line"" : [{""color"" : ""gray""}, {""weight"" : ""200""}]}]}"

Looking at this output, it is not what exactly you wanted, you got an array of objects for properties:) You wanted {"color":"red", "weight": "1rem"} but the output is [{"color":"red"}, {"weight": "1rem"}]

EDIT2: Well, json_object_agg is the solution, so I combined json_object_agg to build the prop objects, now I am thinking this might be made even more simpler. This is the new output from the query.

"{""stil"" : [{""line"" : { ""weight"" : ""200"" }}]}"
"{""style"" : [{""selection"" : { ""color"" : ""red"", ""weight"" : ""1rem"" }}, {""line"" : { ""color"" : ""gray"", ""weight"" : ""200"" }}]}"

this is the fiddle https://www.db-fiddle.com/f/bnLK5FB7cNoth7ey2umcd/0 — hazimdikenli, Jan 28 '22 at 16:03

score 0 · Answer 3 · answered Jan 28 '22 at 20:18

This is trimmed down version, as I thought json_object_agg made things a bit more simpler, so I got rid off some subselects. Tested on postgres 10.

https://www.db-fiddle.com/f/tjzNBoQ3LTbECfEWb9Nrcp/0

;
WITH dtbl as (
    select split_part(tbl.col, '_', 1) as style,
           split_part(tbl.col, '_', 2) as cls,
           split_part(tbl.col, '_', 3) as property_name,
           tbl.val
    from (
             select 'style_selection_color' as col, 'red' as val
             union all
             select 'style_selection_weight', '1rem'
             union all
             select 'style_line_color', 'gray'
             union all
             select 'style_line_weight', '200'
             union all
             select 'stil_line_weight', '200'
         ) as tbl
),
     result as (
         select dtbl.style,
                dtbl.cls,
                json_build_object(dtbl.cls,
                                  (
                                      SELECT json_object_agg(
                                                     nested_props.property_name, nested_props.val
                                                 )
                                      FROM (
                                               SELECT dtbl2.property_name,
                                                      dtbl2.val
                                               FROM dtbl dtbl2
                                               where dtbl2.style = dtbl.style
                                                 and dtbl2.cls = dtbl.cls
                                           ) AS nested_props
                                  )) AS cls_json

         from dtbl
         group by dtbl.style, dtbl.cls)


select json_build_object(
               result.style,
               json_agg(result.cls_json)
           )
from result
group by style
;

You can think of dtbl as your main table, I just added a bonus row called stil similar to other rows, to make sure that grouping is correct.

Here is the output;

{"style":
  [{"line":{"color":"gray", "weight":"200"}},
   {"selection":{"color":"red","weight":"1rem"}}]
}
{"stil":[{"line":{"weight":"200"}}]}

Hierarchically aggregate JSON depending on value in row using PostgreSQL10

3 Answers3