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

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

用 $[*] 把 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。

用 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 不会自动做笛卡尔积，而是分批产生记录，设计查询时不要把它误认为多数组的组合展开。

什么时候应该改成明细表

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

常见问题

JSON_TABLE() 为什么只返回一行？

先检查行源路径是否写成了 $[*]。写成 $[0] 只会匹配数组第一个元素；写成对象路径也不会按数组成员逐个展开。

数组元素是字符串时 PATH 怎么写？

对字符串数组使用 PATH '$'，例如 tag VARCHAR(40) PATH '$'；对对象数组才使用 $.sku 这样的属性路径。

如何保留 JSON 数组原来的顺序？

在 COLUMNS 中增加 pos FOR ORDINALITY，查询末尾按 ORDER BY pos 排序。不要依赖数据库未声明的自然返回顺序。

本文示例依据 MySQL 8.4 Reference Manual 的 JSON Table Functions 整理；实际项目中再结合数据规模和查询模式决定 JSON 与明细表的边界。