MySQL中如何将JSON嵌套任意键map快速转换为键值对表

不用自定义存储函数，直接通过JSON_KEYS + JSON_TABLE原生组合即可实现，省去了手动拼接键值对数组的步骤，性能更优，代码也更简洁：

-- 假设你的JSON存储在表`your_table`的`json_column`字段中
SELECT 
  j.`key`,
  JSON_UNQUOTE(JSON_EXTRACT(yt.json_column, CONCAT('$.map.', j.`key`))) AS `value`
FROM your_table yt
JOIN JSON_TABLE(
  JSON_KEYS(yt.json_column, '$.map'),
  '$[*]' COLUMNS (
    `key` VARCHAR(255) PATH '$'
  )
) j ON TRUE;

如果使用的是MySQL 8.0.21及以上版本，可以用JSON_VALUE简化取值逻辑：

SELECT 
  j.`key`,
  JSON_VALUE(yt.json_column, CONCAT('$.map."', j.`key`, '"')) AS `value`
FROM your_table yt
JOIN JSON_TABLE(
  JSON_KEYS(yt.json_column, '$.map'),
  '$[*]' COLUMNS (`key` VARCHAR(255) PATH '$')
) j ON TRUE;

方案优势 #

• 完全基于原生JSON函数实现，不需要创建额外的存储函数，没有额外的数据库对象依赖，维护成本更低
• 省略了将原始Map转换为键值对数组的步骤，减少了一次JSON序列化/反序列化的开销，处理大型JSON负载时性能提升明显
• 逻辑简单清晰，单条SQL即可完成需求，比存储函数遍历的实现方式可读性更强

注意事项 #

如果Map的键名包含特殊字符（比如空格、点号、斜杠等），需要在构造JSON路径时给键名包裹双引号，避免路径解析错误，示例：

JSON_EXTRACT(yt.json_column, CONCAT('$.map."', j.`key`, '"'))

兼容MySQL 5.7的实现（无JSON_TABLE环境） #

如果使用的是不支持JSON_TABLE的5.7版本，可以用递归CTE实现同样效果，同样不需要存储函数：

WITH RECURSIVE key_idx AS (
  SELECT 
    0 AS idx,
    JSON_KEYS(json_column, '$.map') AS key_arr,
    json_column
  FROM your_table
  UNION ALL
  SELECT idx + 1, key_arr, json_column 
  FROM key_idx 
  WHERE idx < JSON_LENGTH(key_arr) - 1
)
SELECT
  JSON_UNQUOTE(JSON_EXTRACT(key_arr, CONCAT('$[', idx, ']'))) AS `key`,
  JSON_UNQUOTE(JSON_EXTRACT(json_column, CONCAT('$.map."', JSON_UNQUOTE(JSON_EXTRACT(key_arr, CONCAT('$[', idx, ']'))), '"'))) AS `value`
FROM key_idx;

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