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

Answer 1

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"" }}]}"

Answer 2

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"}}]}