MySQL JSON 路径不存在时怎么区分 NULL 和空数组

MySQL 里把 JSON 路径交给 JSON_TABLE() 展开时，路径不存在、值是 JSON null、值是空数组，最后都可能让你看到 NULL 或没有子行。真正稳妥的做法是先判“路径是否存在”，再判 JSON 类型，只有确认是数组后才用长度和展开结果判断。这样，数据缺失和数据为空不会被混成同一种业务状态。

先把路径不存在、JSON null、空数组分成三种状态

假设 order_event.payload 可能出现四类数据：{"items":[...]}、{"items":[]}、没有 items 键，或者 {"items":null}。先做状态识别：

SELECT
  id,
  JSON_CONTAINS_PATH(payload, 'one', '$.items') AS path_exists,
  JSON_TYPE(JSON_EXTRACT(payload, '$.items')) AS json_kind,
  CASE
    -- 只有数组才读取长度，避免把其他类型当成数组处理
    WHEN JSON_TYPE(JSON_EXTRACT(payload, '$.items')) = 'ARRAY'
      THEN JSON_LENGTH(JSON_EXTRACT(payload, '$.items'))
    ELSE NULL
  END AS item_count,
  CASE
    -- 先判断路径，再判断类型和数组长度
    WHEN JSON_CONTAINS_PATH(payload, 'one', '$.items') = 0 THEN '路径不存在'
    WHEN JSON_TYPE(JSON_EXTRACT(payload, '$.items')) = 'NULL' THEN 'JSON null'
    WHEN JSON_TYPE(JSON_EXTRACT(payload, '$.items')) = 'ARRAY'
         AND JSON_LENGTH(JSON_EXTRACT(payload, '$.items')) = 0 THEN '空数组'
    WHEN JSON_TYPE(JSON_EXTRACT(payload, '$.items')) = 'ARRAY' THEN '有元素的数组'
    ELSE '其他 JSON 类型'
  END AS item_state
FROM order_event;

JSON_CONTAINS_PATH() 返回 0，只说明目标路径没有匹配值；它不能说明字段是空数组。路径存在时，再用 JSON_TYPE() 看值的 JSON 类型。只有类型为 ARRAY 时，JSON_LENGTH() 的 0 才代表空数组。JSON null 则是一个存在的 JSON 值，不能按路径缺失处理。

MySQL 官方手册也把这几个边界拆开：JSON_TABLE() 的列路径缺失时会触发 ON EMPTY，JSON null 在结果中按 SQL NULL 返回。也就是说，结果列的 NULL 本身不够表达完整原因，状态列要在展开前保留下来。

用 JSON_TABLE 展开真正存在的数组元素

确认状态后，再把数组元素投影成表格。使用 LEFT JOIN 是为了保留原始订单；当路径缺失或数组为空时，父记录仍在，只是展开列为 NULL。

SELECT
  o.id,
  CASE
    -- 状态识别仍然独立于数组展开
    WHEN JSON_CONTAINS_PATH(o.payload, 'one', '$.items') = 0 THEN '路径不存在'
    WHEN JSON_TYPE(JSON_EXTRACT(o.payload, '$.items')) = 'ARRAY'
         AND JSON_LENGTH(JSON_EXTRACT(o.payload, '$.items')) = 0 THEN '空数组'
    ELSE '待展开'
  END AS item_state,
  jt.item_no,
  jt.sku
FROM order_event AS o
LEFT JOIN JSON_TABLE(
  o.payload,
  '$.items[*]' COLUMNS (
    item_no FOR ORDINALITY,
    -- 缺少 sku 时保留 NULL，不把缺失字段伪装成空字符串
    sku VARCHAR(64) PATH '$.sku' NULL ON EMPTY NULL ON ERROR
  )
) AS jt ON TRUE;

FOR ORDINALITY 给每个元素一个从 1 开始的序号，便于回看原数组位置。sku 是标量列；如果目标值是对象或数组，或者不能转换成目标类型，就会进入 ON ERROR。在这个例子里选择 NULL 是为了让查询继续返回父记录，但生产报表最好另加一个错误状态列，避免把坏数据和真实缺失混在一起。

把 ON EMPTY、ON ERROR 和类型转换分开处理

这三个概念容易在同一条 SQL 里互相遮盖：

如果业务要求缺字段必须暴露，可以把列声明成 ERROR ON EMPTY；如果只是可选字段，则使用默认的 NULL 行为更合适。DEFAULT ... ON EMPTY 中的默认值按 JSON 解析，不要把普通字符串和 JSON 字符串的写法混为一谈。

用一张检查表固定查询和排障口径

遇到“JSON_TABLE 展开后全是 NULL”时，按下面顺序检查，不要先改类型：

1. 确认路径字符串是 $.items 还是 $.items[*]，路径语法错误会直接报错。
2. 用 JSON_CONTAINS_PATH() 确认键是否存在，再用 JSON_TYPE() 确认它是不是数组。
3. 数组类型下再看 JSON_LENGTH()；0 是空数组，大于 0 才有元素可展开。
4. 最后检查列的目标类型、ON EMPTY 和 ON ERROR，并决定是否需要错误状态列。

一句话记忆：先判存在，再判类型，最后判长度；JSON_TABLE() 只负责把已经确认边界的数据展开成行。

相关问题

路径不存在和空数组能不能都用 COALESCE 处理？

不建议直接合并。COALESCE 只能给出一个替代值，不能保留“键不存在”与“键存在但为空数组”的业务含义；先生成状态列更安全。

JSON_TABLE 没有返回子行，是不是 JSON_TABLE 失效了？

不一定。路径缺失和空数组本来就可能没有匹配元素；使用 LEFT JOIN 保留父行，再结合状态列判断原因。

什么时候应该使用 ERROR ON ERROR？

当数据质量要求“对象不能落到标量列”或类型转换失败必须阻断任务时使用。探索性查询或允许脏数据继续流转的报表，才考虑 NULL 或默认值。

参考：MySQL 8.4 JSON Table Functions、MySQL 8.4 JSON Search Functions。