Postgres SQL query, that will group fields in nested JSON objects

Question

I need a SQL query in Postgres that produce a JSON with grouped/inherited data, see example below.

having a table "issues" with following example data:

+--------------------------------------+-------+------------+-----------------------+
|              product_id              | level |  typology  |        comment        |
+--------------------------------------+-------+------------+-----------------------+
| e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5 |     1 | electronic | LED broken            |
| e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5 |     1 | mechanical | missing gear          |
| e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5 |     1 | mechanical | cover damaged         |
| e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5 |     2 | electric   | switch wrong color    |
| e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5 |     2 | mechanical | missing o-ring        |
| e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5 |     2 | electric   | plug wrong type       |
| 3567ae01-c7b3-4cd7-9e4f-85730aab89ee |     1 | mechanical | gear wrong dimensions |
+--------------------------------------+-------+------------+-----------------------+

product_id, typology and comment are string.
level is an integer.

I want to obtain this JSON:

{
  "e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5": {
    "1": {
      "electronic": [ "LED broken" ],
      "mechanical": [ "missing gear", "cover damaged"]
    },
    "2": {
      "electronic": [ "switch wrong color", "plug wrong type" ],
      "mechanical": [ "missing o-ring" ]
    }
  },
  "3567ae01-c7b3-4cd7-9e4f-85730aab89ee": {
    "1": {
      "mechanical": [ "gear wrong dimensions"]
    }
  }
}

So I begun to wrote a query like this:

  SELECT array_to_json(array_agg(json_build_object(
    product_id, json_build_object(
      level, json_build_object(
        typology, comment
      )
    )
  ))) FROM issues

but I didn't realize ho to group/aggregate to obtain the wanted JSON

S-Man · Answer 1 · 2019-09-17T15:59:16.317

step-by-step demo:db<>fiddle

SELECT 
    jsonb_object_agg(key, value)
FROM (
    SELECT
        jsonb_build_object(product_id, jsonb_object_agg(key, value)) as products
    FROM (
        SELECT
            product_id,
            jsonb_build_object(level, jsonb_object_agg(key, value)) AS level
        FROM (
            SELECT
                product_id,
                level,
                jsonb_build_object(typology, jsonb_agg(comment)) AS typology
            FROM
                issues
            GROUP BY product_id, level, typology
        ) s, 
        jsonb_each(typology)
    GROUP BY product_id, level
    ) s,
    jsonb_each(level)
    GROUP BY product_id
) s,
jsonb_each(products)

jsonb_agg() aggregates some values into one JSON array. This has been done with the comments.
After that there is a more complicated step. To aggregate two different JSON objects into one object, you need to do this:

simplified demo:db<>fiddle

First you need to expand the elements into a key and a value column using jsonb_each(). Now you are able to aggregate these two columns using the aggregate function jsonb_object_agg(). See also

This is why the following steps look somewhat difficult. Every level of aggregation (level and product_id) need these steps because you want to merge the elements into single non-array JSON objects.

Because every single aggregation needs separate GROUP BY clauses, every step is done in its own subquery.

Postgres SQL query, that will group fields in nested JSON objects

1 Answers1