MySQL如何按product_id分组将JSON列属性值聚合为单个JSON对象

问题原因 #

你之前的SQL分组依据是id，也就是按variants表的每行单独聚合，所以只能得到单行对应的属性键值对，没有按product_id和属性name做维度聚合。

修正后的SQL（适用于MySQL 8.0及以上版本） #

SELECT 
  product_id,
  JSON_OBJECTAGG(name, value_arr) AS 'aggregated (JSON)'
FROM (
  -- 内层查询：按product_id和属性name分组，聚合得到每个name对应的唯一值数组
  SELECT 
    v.product_id,
    jt.name,
    -- 如果你需要把Size的Xl转为大写和示例一致，可以替换为JSON_ARRAYAGG(DISTINCT UPPER(jt.value))
    JSON_ARRAYAGG(DISTINCT jt.value) AS value_arr
  FROM variants v
  -- 拆分解码properties JSON数组为独立行
  JOIN JSON_TABLE(
    v.properties, 
    '$[*]' COLUMNS (
      name VARCHAR(1024) PATH '$.name' NULL ON EMPTY,
      value VARCHAR(1024) PATH '$.value' NULL ON EMPTY
    )
  ) AS jt ON TRUE
  -- 过滤properties为NULL的无效记录
  WHERE v.properties IS NOT NULL
  GROUP BY v.product_id, jt.name
) t
-- 外层按product_id分组，合并所有属性为最终JSON对象
GROUP BY product_id;

实现逻辑 #

1. 先通过JSON_TABLE把每行的properties JSON数组拆分为多行，每行对应一个属性的name和value，关联对应的product_id
2. 内层按product_id + 属性name分组，用JSON_ARRAYAGG(DISTINCT value)聚合得到每个属性名下的唯一值数组，自动去重
3. 外层再按product_id分组，用JSON_OBJECTAGG把属性名和对应的数组合并成最终的JSON结构，完全匹配你要的输出效果

内容的提问来源于stack exchange，提问作者Maxim Malyk