Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE issues
    ("product_id" varchar(36), "level" int, "typology" varchar(10), "comment" varchar(21))
;
    
INSERT INTO issues
    ("product_id", "level", "typology", "comment")
VALUES
    ('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')
;

CREATE TABLE



INSERT 0 7


SELECT
    product_id,
    level,
    jsonb_build_object(typology, jsonb_agg(comment)) AS typology
FROM
    issues
GROUP BY product_id, level, typology


product_id
level
typology


3567ae01-c7b3-4cd7-9e4f-85730aab89ee
1
{"mechanical": ["gear wrong dimensions"]}

e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5
1
{"electronic": ["LED broken"]}

e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5
2
{"electric": ["switch wrong color", "plug wrong type"]}

e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5
1
{"mechanical": ["missing gear", "cover damaged"]}

e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5
2
{"mechanical": ["missing o-ring"]}



SELECT 5


SELECT
    *
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)


product_id
level
typology
key
value


3567ae01-c7b3-4cd7-9e4f-85730aab89ee
1
{"mechanical": ["gear wrong dimensions"]}
mechanical
["gear wrong dimensions"]

e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5
1
{"electronic": ["LED broken"]}
electronic
["LED broken"]

e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5
2
{"electric": ["switch wrong color", "plug wrong type"]}
electric
["switch wrong color", "plug wrong type"]

e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5
1
{"mechanical": ["missing gear", "cover damaged"]}
mechanical
["missing gear", "cover damaged"]

e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5
2
{"mechanical": ["missing o-ring"]}
mechanical
["missing o-ring"]



SELECT 5


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


product_id
level


e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5
{"2": {"electric": ["switch wrong color", "plug wrong type"], "mechanical": ["missing o-ring"]}}

e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5
{"1": {"electronic": ["LED broken"], "mechanical": ["missing gear", "cover damaged"]}}

3567ae01-c7b3-4cd7-9e4f-85730aab89ee
{"1": {"mechanical": ["gear wrong dimensions"]}}



SELECT 3


SELECT
    *
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)


product_id
level
key
value


e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5
{"2": {"electric": ["switch wrong color", "plug wrong type"], "mechanical": ["missing o-ring"]}}
2
{"electric": ["switch wrong color", "plug wrong type"], "mechanical": ["missing o-ring"]}

e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5
{"1": {"electronic": ["LED broken"], "mechanical": ["missing gear", "cover damaged"]}}
1
{"electronic": ["LED broken"], "mechanical": ["missing gear", "cover damaged"]}

3567ae01-c7b3-4cd7-9e4f-85730aab89ee
{"1": {"mechanical": ["gear wrong dimensions"]}}
1
{"mechanical": ["gear wrong dimensions"]}



SELECT 3


SELECT
    jsonb_build_object(product_id, jsonb_object_agg(key, value))
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


jsonb_build_object


{"e1227f18-0c1f-4ebb-8cbf-a09c74ba14f5": {"1": {"electronic": ["LED broken"], "mechanical": ["missing gear", "cover damaged"]}, "2": {"electric": ["switch wrong color", "plug wrong type"], "mechanical": ["missing o-ring"]}}}

{"3567ae01-c7b3-4cd7-9e4f-85730aab89ee": {"1": {"mechanical": ["gear wrong dimensions"]}}}



SELECT 2


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_object_agg


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



SELECT 1

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

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

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