当前位置：首页 > 文章列表 > 数据库 > MySQL > MySQL JSON 数组怎么展开成多行查询

MySQL JSON 数组怎么展开成多行查询

来源：17golang原创 2026-09-05 22:42:20 0浏览收藏

如果订单表的 items 列保存的是 JSON 数组，查询时不需要在应用层循环，也不必先把数据拆成临时表。MySQL 8.4 支持 JSON_TABLE()：把数组路径写成 $[*]，数组中的每个元素就会成为结果集的一行。

要点速览

$[*] 匹配数组的每个元素，FOR ORDINALITY 可保留原数组顺序。
PATH 负责取字段，缺失值看 ON EMPTY，类型转换失败看 ON ERROR。
频繁按展开字段筛选或建立索引时，应评估把 JSON 明细规范化到子表。

用 $[*] 把 JSON 数组映射成多行

假设 order_record.items 的内容类似 [{"sku":"A100","qty":2},{"sku":"B200","qty":1}]。最小可用写法如下：

SELECT o.order_no, jt.pos, jt.sku, jt.qty
FROM order_record AS o
JOIN JSON_TABLE(
  o.items,
  '$[*]' COLUMNS (
    pos FOR ORDINALITY,
    sku VARCHAR(32) PATH '$.sku',
    qty INT PATH '$.qty'
  )
) AS jt ON TRUE
WHERE o.order_no = 'SO1001'
ORDER BY jt.pos;

这里的 o.items 是输入 JSON，'$[*]' 是行源路径，sku 和 qty 是针对当前数组元素的相对路径。假设数组有两个对象，结果就是两行；pos 从 1 开始，适合恢复原始顺序。JSON_TABLE() 必须有表别名，且在 FROM 中可以引用它前面已经出现的表列，不需要额外写 LATERAL。

MySQL JSON_TABLE 将 order_record.items 数组按 $[*] 展开为 pos、sku、qty 三列的静态关系图 — 图1：从 order_record.items 到 JSON_TABLE 行源，再映射为序号和业务列；这能帮助定位数组路径与字段路径的区别。

用 PATH 和类型声明字段边界

PATH '$.sku' 取当前对象的 sku 属性，PATH '$' 则代表当前匹配到的整个值。列类型必须是标量类型，不能直接把 JSON 对象或数组塞进普通整数列。

生产数据经常存在字段缺失或类型漂移，可以把处理策略写在列定义里：

JSON_TABLE(
  o.items,
  '$[*]' COLUMNS (
    sku VARCHAR(32) PATH '$.sku' ERROR ON EMPTY,
    qty INT PATH '$.qty' DEFAULT '0' ON EMPTY NULL ON ERROR,
    has_note INT EXISTS PATH '$.note'
  )
) AS jt

默认情况下，缺失值是 NULL ON EMPTY，类型转换或把数组保存到标量列出错时是 NULL ON ERROR。对必填业务字段可以使用 ERROR ON EMPTY，让脏数据尽早暴露；对可选数量字段则可以给出默认值。注意顺序应写成 ON EMPTY 再写 ON ERROR，反过来的非标准写法在 MySQL 中已被标记为弃用。

用 NESTED PATH 展开对象里的子数组

如果每个商品还有标签数组，例如 {"sku":"A100","tags":["促销","冷藏"]}，可以在父级列中嵌套一层：

SELECT o.order_no, jt.sku, jt.tag
FROM order_record AS o
JOIN JSON_TABLE(
  o.items,
  '$[*]' COLUMNS (
    sku VARCHAR(32) PATH '$.sku',
    NESTED PATH '$.tags[*]' COLUMNS (
      tag VARCHAR(40) PATH '$'
    )
  )
) AS jt ON TRUE;

一个商品有两个标签时会生成两行，父级的 sku 会随行保留。如果某个商品没有 tags，NESTED PATH 默认仍会产生一行，子列为 NULL，这是外连接式的补充行；只想保留真实标签时，再加 WHERE jt.tag IS NOT NULL。多个同级 NESTED PATH 不会自动做笛卡尔积，而是分批产生记录，设计查询时不要把它误认为多数组的组合展开。

MySQL JSON_TABLE 中 order_record、items、NESTED PATH 与 sku、tag 的父子数组静态关系图 — 图2：父级商品字段与 tags 子数组的静态关系；重点看 NESTED PATH 的子数组边界，以及没有匹配时的 NULL 补充列。

什么时候应该改成明细表

JSON_TABLE() 是查询时的关系投影，不会真的把数组元素写成物理行。它适合结构变化较多、读取频率不高，或只需要在报表查询中临时展开的场景。若系统经常按 sku、qty 过滤、排序、统计，或者需要给明细字段建立稳定索引，通常应把订单和商品明细拆成父子表，再用普通列和索引承担高频查询。

场景	优先方案	判断依据
偶发报表、结构经常变化	JSON_TABLE()	查询时展开即可，减少迁移成本
高频按商品过滤	明细子表	需要普通索引和稳定的执行计划
既要原始文档又要关键字段检索	混合设计	JSON 保留扩展信息，核心字段单独存储