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

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

CodePudding user response：

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

CodePudding user response：

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.