JSON_TABLE 嵌套数组怎么配置或排查

把 JSON 中的多层数组转成行时，JSON_TABLE() 的关键不是把路径写得越长越好，而是让每一层 NESTED PATH 都相对当前父节点展开。一个实用的判断方法是：顶层路径负责“产生父对象”，下一层路径负责“产生子对象”，缺少子数组时 MySQL 可能保留父行并把子列补成 NULL。

先把订单和商品数组拆成父子两层

假设一列 payload 保存订单数组，每个订单有 items 商品数组。不要先把所有字段都写成顶层列；先确定一行代表一个订单，再在同一个 COLUMNS 中嵌套商品路径。

-- 顶层一行代表一个订单，NESTED PATH 一行代表一个商品
SELECT jt.order_no, jt.item_no, jt.item_name
FROM order_import AS oi
JOIN JSON_TABLE(
  oi.payload,
  '$[*]' COLUMNS (
    order_no VARCHAR(32) PATH '$.orderNo',
    NESTED PATH '$.items[*]' COLUMNS (
      item_no INT PATH '$.no',
      item_name VARCHAR(80) PATH '$.name'
    )
  )
) AS jt ON TRUE;

这里的 '$[*]' 会遍历顶层订单，'$.items[*]' 则相对当前订单寻找商品数组。路径写成 '$.orders.items[*]' 反而会多走一层，常见结果就是商品列全为 NULL 或根本没有预期行。

继续展开时，路径要跟着当前父节点走

如果每个商品还有 batches 批次数组，第三层路径应直接写在商品的 COLUMNS 内。此时 '$.batches[*]' 的 $ 指向当前商品，不是原始 JSON 文档的根。

-- 每一行代表一个订单商品批次；路径按嵌套层级相对解析
SELECT jt.order_no, jt.item_no, jt.batch_code, jt.quantity
FROM order_import AS oi
JOIN JSON_TABLE(
  oi.payload,
  '$[*]' COLUMNS (
    order_no VARCHAR(32) PATH '$.orderNo',
    NESTED PATH '$.items[*]' COLUMNS (
      item_no INT PATH '$.no',
      NESTED PATH '$.batches[*]' COLUMNS (
        batch_code VARCHAR(32) PATH '$.code',
        quantity DECIMAL(12,2) PATH '$.quantity'
      )
    )
  )
) AS jt ON TRUE;

排查嵌套数组时，先只保留一层 NESTED PATH，确认父键和子键都有值，再加下一层。这样能快速区分“路径没有匹配”和“更深层数组本来就是空的”。

空数组、缺字段和类型错误要分开处理

没有匹配到嵌套路径时，不一定意味着整条父记录消失。NESTED PATH 默认可以产生一条补全行，嵌套列为 NULL。如果业务要求只有确实有商品的订单，最后再用 WHERE item_no IS NOT NULL 做内连接效果。

字段级排查可加一个存在性列，并把转换策略写清楚：

-- 用 EXISTS PATH 区分字段不存在，用显式策略保护脏数据
SELECT jt.order_no, jt.has_items, jt.quantity
FROM order_import AS oi
JOIN JSON_TABLE(
  oi.payload,
  '$[*]' COLUMNS (
    order_no VARCHAR(32) PATH '$.orderNo',
    has_items INT EXISTS PATH '$.items',
    NESTED PATH '$.items[*]' COLUMNS (
      quantity DECIMAL(12,2) PATH '$.quantity'
        NULL ON EMPTY NULL ON ERROR
    )
  )
) AS jt ON TRUE;

EXISTS PATH 只回答路径上是否有数据，不负责把数组展开。NULL ON EMPTY 处理缺失值，NULL ON ERROR 处理对象、数组或类型转换异常；生产导入若不能接受静默丢失，应把错误记录到旁路表，而不是一律吞掉。

同级嵌套路径为什么会出现“多行但不相乘”

两个 NESTED PATH 写在同一个 COLUMNS 中时，MySQL 会先展开其中一个，再展开另一个，另一组列补 NULL。因此一个订单有 2 个商品、3 个备注，结果通常是 5 行，而不是 6 行。只有当业务明确需要商品与备注的两两组合时，才应先各自展开成派生表，再按订单键连接。

常见问题

JSON_TABLE 的路径为什么不能都从根节点写？

嵌套列的路径相对所属父路径解析；从根节点重写会错过当前订单或商品对象。

如何判断是空数组还是路径写错？

先保留父键和 EXISTS PATH 列，再分别把嵌套路径缩短一层核对。存在数组但子列为空，通常是层级或字段名不匹配。

两个数组需要逐项组合怎么办？

不要依赖同级嵌套路径；把两个数组分别展开成派生表，再用稳定的父键连接，显式表达笛卡尔组合。

把每层数组对应的行含义先写在纸上，再写 JSON_TABLE()，多数“嵌套数组怎么排查”的问题都能缩小到路径层级、空值策略或行数规则中的一项。