You need to enable JavaScript to run this app. MySQL 5.7中如何将JSON列中的数组元素拆分为行查询
优惠活动
大模型
产品
解决方案
定价
更多

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即可得到和需求完全匹配的输出结果:

personIdname
1one
1two
2four

内容的提问来源于stack exchange,提问作者Josh M.

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.20 14:24:07