2

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?

Maxim Malyk
  • 31
  • 4
  • 1
    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 Dec 01 '21 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 Dec 11 '21 at 21:12

1 Answers1

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;
Maxim Malyk
  • 31
  • 4