MySQL 5.7中如何将JSON列中的数组元素拆分为行查询
MySQL 5.7 JSON数组元素拆行查询方案
现有SQL的失效原因
- 语法层面不兼容MySQL 5.7规则:
JSON_EXTRACT是用于提取JSON值的函数,返回单值结果,不能直接作为关联的数据源派生表使用 - 能力缺口:MySQL 5.7没有内置
JSON_TABLE(该函数为8.0版本新增的JSON转表能力),无法直接将JSON数组映射为多行结果,必须通过「整数序列遍历下标」的方式实现拆行逻辑。
实现原理
JSON数组的元素是按下标从0开始顺序存储的,只要能为每个数组匹配到从0到数组长度-1的连续整数,就可以通过$[下标]路径逐个提取元素:
- 先用
JSON_LENGTH(p.data)计算每条记录对应的JSON数组总元素数 - 关联一组覆盖最大数组长度的连续整数序列
- 关联条件设置为「整数序号 < 数组长度」,让每条Person记录自动关联出和数组元素数量完全一致的行数
- 按关联的整数序号作为下标,提取对应位置的元素值
可直接运行的SQL代码
SELECT p.id AS personId, JSON_UNQUOTE(JSON_EXTRACT(p.data, CONCAT('$[', seq.num, '].name'))) AS name FROM Person p INNER JOIN ( -- 生成从0开始的连续整数序列,根据业务中最长的JSON数组长度调整行数即可 SELECT 0 AS num UNION ALL SELECT 1 AS num ) seq ON seq.num < JSON_LENGTH(p.data) WHERE JSON_UNQUOTE(JSON_EXTRACT(p.data, CONCAT('$[', seq.num, '].name'))) <> 'three';
代码匹配说明
- 关联条件
seq.num < JSON_LENGTH(p.data)会自动过滤超出数组长度的序号行,比如长度为2的数组只会匹配num=0、num=1两行,不会生成多余空行 - 提取时直接把路径拼接到
$[下标].name层级,简化嵌套提取的写法 - 用
JSON_UNQUOTE去掉JSON字符串值自带的双引号,输出和预期完全一致的纯文本名称
实用优化技巧
如果业务场景中的JSON数组长度较大,不需要手动编写大量UNION ALL语句生成序列,可以借助MySQL自带的系统表(比如information_schema.COLUMNS)通过笛卡尔积快速生成大范围连续整数,省去维护手写数字序列的成本。
执行上述SQL即可得到和需求完全匹配的输出结果:
personId name 1 one 1 two 2 four
内容的提问来源于stack exchange,提问作者Josh M.
相关产品推荐
相关产品推荐

