MySQL JSON_TABLE 怎样展开嵌套数组又保留没有子项的行

把订单 JSON 转成关系行时，最容易踩的坑不是数组展开，而是“没有明细的订单去哪了”。MySQL 的 JSON_TABLE() 配合 NESTED PATH 时，外层对象仍然可以保留；嵌套列会用 NULL 补出一行。只有你后面再用条件过滤掉这些 NULL，才会主动变成内连接效果。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/json-table-functions.html

先看外层对象为什么会丢失

假设一列 payload 存着订单数组，每个订单可能有 items。顶层路径决定外层行，嵌套路径决定子项行。不要把“没有子项”理解成“没有订单”：两者是不同层级的事实。

-- 先把每个订单对象作为一条外层记录
SELECT order_id, payload
FROM order_events;

如果只写一个指向 $.items[*] 的行路径，空数组自然没有匹配结果，外层订单也就无法独立出现。要保留订单，应该让 $[*] 负责订单对象，再在 COLUMNS 里放 NESTED PATH。

用 NESTED PATH 展开明细数组

下面的写法把订单编号放在外层，把明细编号和数量放在嵌套路径中。每个明细对象对应一行，订单编号会随着外层关系带到结果中。

-- 外层路径产生订单，嵌套路径产生明细
SELECT jt.order_no, jt.item_no, jt.qty
FROM JSON_TABLE(
  '[{"order_no":"A100","items":[{"item_no":"P01","qty":2},{"item_no":"P02","qty":1}]},{"order_no":"A101"}]',
  '$[*]' COLUMNS (
    order_no VARCHAR(20) PATH '$.order_no',
    NESTED PATH '$.items[*]' COLUMNS (
      item_no VARCHAR(20) PATH '$.item_no',
      qty INT PATH '$.qty'
    )
  )
) AS jt;

这里的关键不是把数组“拍平”，而是明确两种行粒度：order_no 属于外层对象，item_no 和 qty 属于子路径。这样后续增加金额、仓库等明细字段时，仍能看出它们应该放在哪个 COLUMNS 层级。

处理没有子项的外层行

当 A101 没有 items 时，MySQL 文档定义的行为是保留外层行，嵌套路径列用 NULL 补齐。这个 NULL 表示“该嵌套路径没有匹配”，不等于 JSON 中明细字段显式写了 JSON null。

-- 用外层键识别订单，用子项键判断是否真的有明细
SELECT
  jt.order_no,
  jt.item_no,
  jt.qty,
  CASE WHEN jt.item_no IS NULL THEN 'no-item' ELSE 'has-item' END AS item_state
FROM JSON_TABLE(
  '[{"order_no":"A100","items":[{"item_no":"P01","qty":2}]},{"order_no":"A101","items":[]}]',
  '$[*]' COLUMNS (
    order_no VARCHAR(20) PATH '$.order_no',
    NESTED PATH '$.items[*]' COLUMNS (
      item_no VARCHAR(20) PATH '$.item_no',
      qty INT PATH '$.qty'
    )
  )
) AS jt;

业务上如果允许明细编号为空，就不要只靠 item_no IS NULL 判断是否有子项，最好增加一个必填的子对象标识，或把存在性字段单独映射出来。否则“真实字段为空”和“没有补行”可能被混在一起。

用 WHERE 控制内连接还是外连接

NESTED PATH 的默认效果接近外连接，但后面的 WHERE 可以把它改成内连接。最常见的误删写法是直接过滤子项列。

-- 这个条件会主动去掉没有明细的订单
SELECT jt.order_no, jt.item_no
FROM JSON_TABLE(
  '[{"order_no":"A100","items":[{"item_no":"P01"}]},{"order_no":"A101"}]',
  '$[*]' COLUMNS (
    order_no VARCHAR(20) PATH '$.order_no',
    NESTED PATH '$.items[*]' COLUMNS (item_no VARCHAR(20) PATH '$.item_no')
  )
) AS jt
WHERE jt.item_no IS NOT NULL;

如果报表要显示所有订单，应把筛选写在聚合或条件统计内部，或者显式保留 item_no IS NULL 的补行。若报表只关心确实有明细的订单，上面的过滤就是有意的内连接，不是 JSON_TABLE() 丢数据。

嵌套路径与兄弟路径的边界

同一个 COLUMNS 中出现两个兄弟 NESTED PATH 时，MySQL 按文档说明分别产生记录，另一条路径的列在当前记录中补 NULL；总行数是各路径产生行数的相加，不是两组数组长度相乘。这个规则与手写两个独立连接不同。

-- 兄弟嵌套路径是并列输出，不是自动笛卡尔积
SELECT jt.order_no, jt.item_no, jt.discount_code
FROM JSON_TABLE(
  '[{"order_no":"A100","items":[{"item_no":"P01"}],"discounts":[{"code":"D1"}]}]',
  '$[*]' COLUMNS (
    order_no VARCHAR(20) PATH '$.order_no',
    NESTED PATH '$.items[*]' COLUMNS (item_no VARCHAR(20) PATH '$.item_no'),
    NESTED PATH '$.discounts[*]' COLUMNS (discount_code VARCHAR(20) PATH '$.code')
  )
) AS jt;

如果业务真正需要“每个明细和每个优惠”的组合，要先确认这是笛卡尔积需求，不能把兄弟 NESTED PATH 的默认行数规则当成组合结果。

整理可上线的查询检查点

一句话总结：让顶层路径负责“谁必须保留”，让 NESTED PATH 负责“子数组怎样展开”，再单独决定是否过滤补出的 NULL 行。这样 JSON 转关系表时，行数变化就有清楚的层级依据。

相关问题

JSON_TABLE 没有子数组时一定返回一行吗？ 使用 NESTED PATH 时，外层行可以保留，嵌套列通常以 NULL 补齐；最终是否保留还要看后续筛选。

为什么 WHERE item_no IS NOT NULL 会少订单？ 因为它过滤掉了无匹配嵌套路径产生的补行，效果等同于只保留有明细的订单。

两个 NESTED PATH 为什么不是笛卡尔积？ 兄弟路径按各自匹配分别产生记录，官方规则是行数相加；要组合两组数组，需要单独设计关系。