MySQL: JSON column values aggregate into the single JSON object groupped by property

Question

I have a table variants with properties column type JSON and some data:

id	product_id	properties(JSON)
1	1	`[{"name": "Color", "value": "Black"}, {"name": "Size", "value": "Xl"}]`
2	1	`[{"name": "Color", "value": "Red"}, {"name": "Size", "value": "Xl"}]`
3	1	`[{"name": "Color", "value": "White"}, {"name": "Size", "value": "L"}]`
4	2	`[{"name": "Type", "value": "Circle"}]`
5	2	`[{"name": "Type", "value": "Box"}]`
6	3	NULL

I need to get aggregated rows by properties name and product_id where each property has an array of unique values. The expected result is:

product_id	aggregated (JSON)
1	`{"Color":["Red", "Black", "White"], "Size": ["XL", "L"]}`
2	`{"Type": ["Circle", "Box"]}`

I tried to get objects instead of arrays but stuck for the next step.

SELECT product_id, JSON_OBJECTAGG(jt.name, jt.value) AS json
FROM variants,
     JSON_TABLE(properties, '$[*]' COLUMNS (
         name VARCHAR(1024) PATH '$.name' NULL ON EMPTY,
         value VARCHAR(1024) PATH '$.value' NULL ON EMPTY)
         ) AS jt
GROUP BY id;

Result:

product_id	properties(JSON)
1	`{"Color": "Black", "Size": "Xl"}`
1	`{"Color": "Red", "Size": "Xl"}`
1	`{"Color": "White", "Size: "L"}`
2	`{"Type": "Circle"}`
2	`{"Type": "Box"}`

How can I merge it then?

Your "aggregation" does not make sense. Your data in variants table is basically product 1 Black XL, product 1 Red XL and product 1 White L. This is not the same as product 1 (Black or Red or White) (XL or L) as indicated by your "aggregation" which suggests 6 variants of product 1. — user1191247
– user1191247, Commented Dec 1, 2021 at 12:54
@nnichols I want to collect all the unique values of each property. The Next step will be to find missed combinations. — Maxim Malyk
– Maxim Malyk, Commented Dec 11, 2021 at 21:12

Maxim Malyk · Accepted Answer · 2021-12-11 21:13:23Z

1

I found that I have to collect values separately from names to aggregate them later into JSON_OBJECT.

WITH props_values(product_id, property_name, values_agg) AS (
    SELECT product_id,
           jt.name,
           JSON_KEYS(JSON_OBJECTAGG(jt.value, ''))
    FROM variants,
         JSON_TABLE(
                 properties,
                 '$[*]'
                 COLUMNS (
                     name VARCHAR(1024) PATH '$.name' NULL ON EMPTY,
                     value VARCHAR(1024) PATH '$.value' NULL ON EMPTY
                     )
             ) AS jt
    WHERE jt.name IS NOT NULL
      AND jt.name <> ''
      AND jt.value IS NOT NULL
      AND jt.value <> ''
    GROUP BY product_id, jt.name
)
SELECT product_id, JSON_OBJECTAGG(property_name, values_agg) AS properties_agg
FROM props_values
GROUP BY product_id;

answered Dec 11, 2021 at 21:13

Maxim Malyk

314 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL: JSON column values aggregate into the single JSON object groupped by property

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related