除JSON_TABLE外 MySQL中JSON数组转结构化表有哪些单SQL实现方案？

不需要升级到MySQL 8.x版本，MySQL 5.7及以上版本就可以通过原生JSON函数配合手动构造的数字序列实现需求，具体方案如下：

实现思路 #

• 手动构造连续整数序列，覆盖JSON数组的最大长度（MySQL JSON数组下标从0开始）
• 将原表与整数序列做笛卡尔积关联，过滤掉下标超过当前行JSON数组长度的无效关联
• 通过JSON提取函数按下标获取对应位置的key和value值即可

示例SQL #

假设你的表名为your_table，且JSON数组最多有10个元素，SQL示例如下：

SELECT 
  t.id,
  JSON_EXTRACT(t.col, CONCAT('$[', n.idx, '].key')) AS `key`,
  JSON_EXTRACT(t.col, CONCAT('$[', n.idx, '].value')) AS `value`
FROM your_table t
JOIN (
  -- 手动构造下标序列，可根据实际数组最大长度扩展
  SELECT 0 AS idx 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
WHERE n.idx < JSON_LENGTH(t.col)
ORDER BY t.id, n.idx;

注意事项 #

• 如果你的JSON数组长度更长，只需要在子查询n中继续追加UNION ALL SELECT x即可，比如最长100个元素就写到99
• 如果你需要提取的key/value是字符串类型，想要去掉外层引号，可以用JSON_UNQUOTE函数包裹JSON_EXTRACT，或者直接用->>运算符，比如t.col->>CONCAT('$[', n.idx, '].key')
• 该方案不需要创建临时表、不需要使用JSON_TABLE函数，仅单条SQL即可实现，兼容MySQL 5.7及以上所有版本

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