MySQL JSON_TABLE 嵌套数组为空时为什么还会返回一行

使用 JSON_TABLE() 展开订单明细时，如果某个对象的 items 是空数组，结果里仍然出现一行、但明细列全是 NULL，通常不是 MySQL 多返回了数据，而是 NESTED PATH 为父级记录保留的补位行。要只留下真实明细，应先标记数组是否有元素，再在外层过滤；ON EMPTY 主要处理路径取不到值时的列值，不负责取消这行。

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

判断这类结果时，先把“父级对象是否保留”和“嵌套数组是否匹配”分开看：根路径命中了对象，嵌套路径没有命中元素，MySQL 就可能保留父级并用 NULL 补齐嵌套列。

为什么 NESTED PATH 会补出一行

JSON_TABLE() 把 JSON 文档投影成关系表。根路径 $[*] 负责找到每个对象，根级列例如 order_id 直接从对象取值；NESTED PATH '$.items[*]' 再尝试把对象内的数组展开成多行。

关键在于两层路径承担的责任不同。只要根路径找到订单对象，父级记录就有存在理由；当 items[*] 没有元素时，嵌套列没有可填入的值，于是会形成一行 NULL 补位。它表达的是“这个订单存在，但当前没有可展开的明细”，不是一条 sku 为空的真实商品记录。

-- 用一条有明细、 一条空数组的订单区分两种输入结构。
SET @orders = JSON_ARRAY(
  JSON_OBJECT('order_id', 1001, 'items', JSON_ARRAY(
    JSON_OBJECT('sku', 'A-01', 'qty', 2)
  )),
  JSON_OBJECT('order_id', 1002, 'items', JSON_ARRAY())
);

-- 根级列保留订单号，NESTED PATH 负责展开 items 数组。
SELECT jt.order_id, jt.sku, jt.qty
FROM JSON_TABLE(
  @orders,
  '$[*]' COLUMNS(
    order_id INT PATH '$.order_id',
    NESTED PATH '$.items[*]' COLUMNS(
      sku VARCHAR(30) PATH '$.sku',
      qty INT PATH '$.qty'
    )
  )
) AS jt;

这个查询的关注点不是具体输出排版，而是行的来源：1001 有真实明细，1002 的数组没有元素，嵌套列没有对应值。排查时不要仅凭 sku IS NULL 就断言数据坏了，因为合法的明细也可能允许 sku 为空。

用 EXISTS PATH 标记空数组

如果业务需要同时保留订单概览和明细查询，可以在根级列中增加一个存在性标记。EXISTS PATH 返回路径是否能找到匹配内容，标记字段会和 JSON_TABLE 行集一起输出。这样筛选条件不必借用业务列的 NULL 语义。

-- has_items 只表达 items[*] 是否有可展开的数组元素。
SELECT jt.order_id, jt.has_items, jt.sku, jt.qty
FROM JSON_TABLE(
  @orders,
  '$[*]' COLUMNS(
    order_id INT PATH '$.order_id',
    has_items TINYINT EXISTS PATH '$.items[*]',
    NESTED PATH '$.items[*]' COLUMNS(
      sku VARCHAR(30) PATH '$.sku',
      qty INT PATH '$.qty'
    )
  )
) AS jt;

这里的 has_items 是结构判断，不是库存数量，也不是对 qty 的校验。空数组的订单可以显示为 0，有元素的订单显示为 1；如果你还要保留订单级统计，就不要在最内层盲目过滤。

在外层筛掉补位行

只需要真实明细时，可以把 JSON_TABLE 放进派生表或公共表表达式，再用标记过滤。把条件写在外层的好处是：先完成 JSON 到关系列的映射，再明确表达“结果集不要空数组补位行”。

-- 先生成结构标记，再在外层只选择有真实明细的行。
SELECT x.order_id, x.sku, x.qty
FROM (
  SELECT jt.order_id, jt.has_items, jt.sku, jt.qty
  FROM JSON_TABLE(
    @orders,
    '$[*]' COLUMNS(
      order_id INT PATH '$.order_id',
      has_items TINYINT EXISTS PATH '$.items[*]',
      NESTED PATH '$.items[*]' COLUMNS(
        sku VARCHAR(30) PATH '$.sku',
        qty INT PATH '$.qty'
      )
    )
  ) AS jt
) AS x
-- 过滤结构标记，不把合法的 NULL 业务字段当成空数组。
WHERE x.has_items = 1;

如果一条明细允许 sku 为空，这种写法比 WHERE sku IS NOT NULL 更稳。如果你的输入保证 sku 永远非空，后者也能工作，但应把这个前提写进数据约束或接口约定，而不是让查询读者猜。

ON EMPTY 处理的是列值，不是行数

ON EMPTY 用于路径没有找到值时的列行为，常见选择是默认置 NULL、提供默认 JSON 值，或在缺失时抛错。它解决的是“列怎么填”，而 NESTED PATH 解决的是“嵌套结构如何映射”。两者放在同一条语句里，不代表设置了默认值就会删除补位行。

因此排查可以按三个问题拆开：第一，根路径是否找到父级对象；第二，嵌套路径是否找到数组元素；第三，命中后的字段缺失时应该使用 NULL、默认值还是错误。只有第三个问题属于 ON EMPTY 的直接职责。

常见问题

空数组补位行算不算重复数据？

通常不算。它是父级对象与嵌套路径之间的结构结果；是否保留取决于你是在做订单概览，还是只要明细行。

能不能直接用 sku IS NOT NULL？

可以，但前提是业务明确禁止真实明细的 sku 为空。更通用的做法是使用 EXISTS PATH 产生结构标记，再按标记筛选。

ON EMPTY 能让空数组不生成行吗？

不能把它当作行过滤开关。它主要影响匹配行里的列值；要控制结果集，应使用结构标记或外层查询条件。

记住一个判断顺序：先看根对象是否命中，再看嵌套数组有没有元素，最后才处理字段缺失的默认值。这样遇到 JSON_TABLE 返回 NULL 明细时，就能快速区分补位行和真实脏数据。