MySQL查询中从JSON对象属性生成独立JSON对象列表的实现方法

实现代码 #

适用版本：MySQL 5.7.22 及以上（支持JSON_ARRAYAGG） #

无需手动处理JSON字符串截取，直接通过原生JSON函数完成拆分、排序、聚合：

SELECT 
  业务主键列,
  JSON_ARRAYAGG(JSON_OBJECT(`key`, `value`)) AS 结果字段
FROM (
  SELECT 
    t.业务主键列,
    -- 提取单个JSON键
    JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(t.存储JSON的字段名), CONCAT('$[', n.n, ']'))) AS `key`,
    -- 提取对应键的值
    JSON_EXTRACT(t.存储JSON的字段名, CONCAT('$.', JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(t.存储JSON的字段名), CONCAT('$[', n.n, ']'))))) AS `value`
  FROM 你的业务表名 t
  -- 关联数字辅助序列，可根据实际JSON最大键数量扩展序列长度
  INNER JOIN (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) n ON n.n < JSON_LENGTH(t.存储JSON的字段名)
  ORDER BY `key` ASC
) tmp
GROUP BY 业务主键列;

示例中将业务主键列、存储JSON的字段名、你的业务表名替换为实际的表/字段即可。

适用版本：MySQL 5.7.22 以下（无JSON_ARRAYAGG） #

使用GROUP_CONCAT拼接实现数组聚合：

SELECT 
  业务主键列,
  CAST(CONCAT('[', GROUP_CONCAT(JSON_OBJECT(`key`, `value`) ORDER BY `key` ASC), ']') AS JSON) AS 结果字段
FROM (
  SELECT 
    t.业务主键列,
    JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(t.存储JSON的字段名), CONCAT('$[', n.n, ']'))) AS `key`,
    JSON_EXTRACT(t.存储JSON的字段名, CONCAT('$.', JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(t.存储JSON的字段名), CONCAT('$[', n.n, ']'))))) AS `value`
  FROM 你的业务表名 t
  INNER JOIN (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) n ON n.n < JSON_LENGTH(t.存储JSON的字段名)
) tmp
GROUP BY 业务主键列;

方案说明 #

• 全程使用MySQL原生JSON函数实现，兼容键名含特殊字符、值为嵌套JSON等复杂场景，避免手动trim截取字符串的异常问题
• 数字辅助序列可根据实际业务中单个JSON对象的最大键数灵活扩展，比如最大有20个键就补充到SELECT 19即可

内容的提问来源于stack exchange，提问作者Borys Zielonka