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 · Accepted Answer · 2019-09-17 15:59:16Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related