MySQL JSON_TABLE NESTED PATH 没匹配时怎样保留父行

在 MySQL 8.4 中，JSON_TABLE() 的 NESTED PATH 没有匹配项时，默认不会把顶层父对象整行丢掉，而是保留父列，并为嵌套列生成一条值为 NULL 的补行。这个行为可以理解为顶层行与嵌套行之间的外连接；只有明确写过滤条件，才会把它收窄成“必须有子项”的结果。

记住两点：NESTED PATH 的无匹配结果是“父行保留、子列补 NULL”；FOR ORDINALITY 负责追踪行来自哪个父对象或哪个子对象。不要用是否出现子行来猜测父对象是否存在。

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

先看清 JSON_TABLE 的行是怎样生成的

JSON_TABLE() 先根据最外层路径生成行源，再按 COLUMNS 中的列定义取值。顶层路径例如 '$[*]' 会让数组中的每个对象成为一条父行；NESTED PATH '$.items[*]' 则在每个父对象下面继续展开子数组。

因此，下面这份数据里，订单 1001 有两个商品，订单 1002 没有 items，订单 1003 的商品数组为空：

SELECT *
FROM JSON_TABLE(
  '[
    {"order_id": 1001, "items": [{"sku": "A"}, {"sku": "B"}]},
    {"order_id": 1002},
    {"order_id": 1003, "items": []}
  ]',
  '$[*]' COLUMNS (
    order_id INT PATH '$.order_id',
    NESTED PATH '$.items[*]' COLUMNS (
      sku VARCHAR(20) PATH '$.sku'
    )
  )
) AS jt;

顶层路径负责产生三个父对象。嵌套路径对 1001 找到两项，对 1002 和 1003 都找不到匹配项。关键是：找不到嵌套项并不等于顶层对象不存在。

NESTED PATH 没匹配为什么仍然有父行

MySQL 文档把这个行为定义为外连接效果：NESTED PATH 没有匹配时，嵌套列全部设为 NULL，同时产生一条补行。把上面的查询想象成结果集，得到的结构大致是：

这条规则适合“即使没有明细，也要列出主表对象”的报表。例如订单对账、目录树检查和数据完整性盘点，都可能需要把没有子项的父对象显式展示出来。如果后续把 NULL 当成“没有父对象”，就会误判数据。

用默认值区分缺失列和 NULL 补行

NESTED PATH 产生补行后，嵌套列也会参与列级的 ON EMPTY 规则。默认的 NULL ON EMPTY 会得到 NULL；如果业务报表需要可读的占位值，可以在嵌套列上指定 DEFAULT。

SELECT *
FROM JSON_TABLE(
  '[
    {"order_id": 1001, "items": [{"sku": "A"}]},
    {"order_id": 1002}
  ]',
  '$[*]' COLUMNS (
    order_id INT PATH '$.order_id',
    NESTED PATH '$.items[*]' COLUMNS (
      -- 子路径没有匹配时，用文本占位而不是丢掉父行
      sku VARCHAR(20) PATH '$.sku' DEFAULT 'NO_ITEM' ON EMPTY
    )
  )
) AS jt;

这里的 DEFAULT 只改变嵌套列的取值，不会改变行生成规则。1002 仍然有一条父行，只是 sku 由 NULL 变成了 NO_ITEM。不要把它和 ON ERROR 混为一谈：前者处理路径缺失，后者处理对象转标量等转换错误。

用 FOR ORDINALITY 追踪父子记录关系

当父对象的业务字段可能重复时，单看 order_id 不足以判断两组子项属于哪个父对象。FOR ORDINALITY 会从 1 开始为当前 COLUMNS 层产生的行编号，因此可以在结果集中保存父层序号和子层序号。

SELECT *
FROM JSON_TABLE(
  '[
    {"customer": "same", "items": [{"sku": "A"}]},
    {"customer": "same", "items": [{"sku": "B"}, {"sku": "C"}]},
    {"customer": "empty"}
  ]',
  '$[*]' COLUMNS (
    -- 父层序号用来区分两个 customer 值相同的对象
    top_ord FOR ORDINALITY,
    customer VARCHAR(20) PATH '$.customer',
    NESTED PATH '$.items[*]' COLUMNS (
      -- 子层序号只在同一个父对象的子项中递增
      item_ord FOR ORDINALITY,
      sku VARCHAR(20) PATH '$.sku'
    )
  )
) AS jt
ORDER BY top_ord, item_ord;

这样得到的 top_ord 能回答“来自第几个顶层对象”，item_ord 能回答“在该对象中是第几个子项”。如果某个父对象没有 items，它仍会出现，但嵌套层的序号和 sku 都会是 NULL。

什么时候要用 WHERE 把补行过滤掉

如果查询目标是“只要确实存在子项的父对象”，可以在外层对嵌套列添加条件。这个条件不是在改变 NESTED PATH 的行生成规则，而是在结果生成之后排除补行。

SELECT order_id, sku
FROM JSON_TABLE(
  '[
    {"order_id": 1001, "items": [{"sku": "A"}]},
    {"order_id": 1002}
  ]',
  '$[*]' COLUMNS (
    order_id INT PATH '$.order_id',
    NESTED PATH '$.items[*]' COLUMNS (
      sku VARCHAR(20) PATH '$.sku'
    )
  )
) AS jt
-- 只保留有实际子项的父对象，NULL 补行会被排除
WHERE sku IS NOT NULL;

过滤前适合做“主对象全量盘点”，过滤后适合做“明细存在性查询”。如果 sku 本身允许合法的 JSON null，就不要把它当作唯一判据，可以额外增加 EXISTS PATH 标记或选择一个不会为空的业务字段。

多个 NESTED PATH 不会自动做笛卡尔积

同一层写多个兄弟 NESTED PATH 时，MySQL 会依次处理它们；一个嵌套路径产出记录时，另一个嵌套路径的列会补成 NULL。因此总行数更接近各路径产出行数的相加，而不是两个子数组长度的相乘。

SELECT *
FROM JSON_TABLE(
  '{"a": [1, 2], "b": [10, 20]}',
  '$' COLUMNS (
    NESTED PATH '$.a[*]' COLUMNS (
      -- 第一组子数组产生两行，b 列在这些行中为空
      a_value INT PATH '$'
    ),
    NESTED PATH '$.b[*]' COLUMNS (
      -- 第二组子数组随后产生两行，a 列在这些行中为空
      b_value INT PATH '$'
    )
  )
) AS jt;

如果业务真的需要把两个数组按位置配对，应该先确认配对规则，再分别展开并用序号或其他键连接；不能看到两个 NESTED PATH 就假设会得到四行两两组合。

排查 JSON_TABLE 结果时的判断清单

常见问题

NESTED PATH 没有匹配时能不能让父行完全消失？

可以在外层对可靠的嵌套列使用 WHERE ... IS NOT NULL，或者使用 EXISTS PATH 生成存在性标记后过滤。默认行为仍然是保留父行。

NULL 补行是 ON ERROR 造成的吗？

不是。它来自 NESTED PATH 没有匹配时的补行语义；ON ERROR 处理的是对象、数组转标量或类型转换等错误。

FOR ORDINALITY 的序号能当业务主键吗？

不能把它当跨查询稳定的业务主键。它只表示当前 JSON_TABLE 结果中行的顺序，适合本次查询内追踪父子来源和构造临时关联。

为什么没有 items 的对象仍然返回一行？

因为顶层路径已经匹配到了该对象，NESTED PATH 只负责展开子路径；子路径无匹配时按外连接语义生成 NULL 补行。需要明细时再显式过滤。