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

user_7357936 0
Viewed 229

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?

aggregates mysql sql json
1 Answers

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;
user_73579360
Related