MySQL JSON_TABLE怎样把缺失字段和 JSON null 区分开

用 MySQL JSON_TABLE() 把半结构化数据转成列时，我最容易踩到的坑不是路径写错，而是把“字段不存在”和“字段明确写成 JSON null”看成同一件事。普通的 PATH 列在这两种情况下都可能得到 SQL NULL，后续清洗、补默认值或统计时就会失去原始语义。

更稳妥的做法是把“字段是否出现”和“字段取出的值”拆成两列：用 EXISTS PATH 保存存在性，用普通 PATH 列保存值，再根据 ON EMPTY 和 ON ERROR 处理缺失与类型错误。

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

先把三种状态分开

假设输入文档中有三种记录：

• {"name":"Ada"}：字段存在，值是字符串。
• {}：字段缺失，路径找不到值。
• {"name":null}：字段存在，但值明确是 JSON null。

如果只写一个 name VARCHAR(64) PATH '$.name'，后两种情况都可能表现为 SQL NULL。这时查询结果无法回答“用户没有传这个字段”还是“用户传了 null”。因此要把存在性变成显式标记。

用 EXISTS PATH 记录字段是否出现

EXISTS PATH 列只回答一个问题：指定路径上是否有数据。它通常使用整数类型，返回 1 或 0。把它和普通值列放在同一个 COLUMNS 中，就能保留两个维度。

SELECT jt.name_present, jt.name_value
FROM JSON_TABLE(
  '[{"name":"Ada"},{},{"name":null}]',
  '$[*]' COLUMNS(
    -- 只记录 $.name 是否出现，不负责返回姓名
    name_present INT EXISTS PATH '$.name',
    -- 读取姓名；缺失值默认按 NULL 处理
    name_value VARCHAR(64) PATH '$.name' NULL ON EMPTY NULL ON ERROR
  )
) AS jt;

这组列的解释是：

关键点是第三行：值列仍然是 NULL，但存在性列为 1，所以它和第二行可以区分。这个标记也适合写入中间表，之后再决定是否覆盖旧值。

ON EMPTY 和 ON ERROR 怎么配

这两个子句处理的是不同问题。ON EMPTY 针对路径没有匹配值的情况；ON ERROR 针对取到的 JSON 值无法按目标列类型保存的情况，例如把对象或数组写进标量列。它们不是用来替代 EXISTS PATH 的，因为默认值只能改变输出值，不能告诉你字段是否出现。

SELECT jt.name_present, jt.name_value
FROM JSON_TABLE(
  '[{"name":"Ada"},{},{"name":{"raw":true}}]',
  '$[*]' COLUMNS(
    -- 存在性独立于值处理，便于区分缺失和显式 null
    name_present INT EXISTS PATH '$.name',
    -- 缺失时保留占位文本；类型无法转换时返回 NULL
    name_value VARCHAR(64) PATH '$.name'
      DEFAULT '未提供' ON EMPTY
      NULL ON ERROR
  )
) AS jt;

若业务要求输入严格，建议把 ERROR ON EMPTY 或 ERROR ON ERROR 用在导入边界，让脏数据尽早失败；若业务允许渐进清洗，可以用 DEFAULT 记录占位值，同时保留 EXISTS PATH 供后续判断。

把判断放进实际查询

在真实表中，JSON 文档通常来自一列。JSON_TABLE() 可以引用前面表的列，适合在查询阶段先展开状态，再决定更新策略。

SELECT
  e.id,
  jt.nickname_present,
  jt.nickname_value,
  CASE
    -- 未出现和显式 null 走不同业务分支
    WHEN jt.nickname_present = 0 THEN '保持原值'
    WHEN jt.nickname_value IS NULL THEN '清空昵称'
    ELSE '更新昵称'
  END AS action_name
FROM event_payload AS e
JOIN JSON_TABLE(
  e.payload,
  '$' COLUMNS(
    -- 先保留键是否出现的事实
    nickname_present INT EXISTS PATH '$.nickname',
    -- 再读取可写入的字符串值
    nickname_value VARCHAR(64) PATH '$.nickname' NULL ON EMPTY NULL ON ERROR
  )
) AS jt;

这里的 CASE 顺序很重要：先判断存在性为 0，再判断值是否为 SQL NULL。如果先只写 nickname_value IS NULL，缺失字段和显式 JSON null 就会重新合并。

常见误区和选择建议

• 误区一：用 COALESCE 判断是否缺失。 COALESCE 只能处理结果值，无法恢复字段是否出现；应保留 EXISTS PATH。
• 误区二：把 DEFAULT 当成存在性标记。 缺失字段被填成默认文本后，原始状态已经被覆盖；需要同时保留标记列。
• 误区三：把 ON ERROR 当成缺失处理。 路径不匹配属于 ON EMPTY，对象、数组或类型转换失败属于 ON ERROR。
• 误区四：用字符串比较 JSON null。 JSON null 不是字符串 "null"；若业务必须区分它与缺失，使用存在性列加值列，不要靠文本替换。

一张表记住组合方式

结语

要区分 MySQL JSON_TABLE() 中的字段缺失和显式 JSON null，不要试图从一个普通值列里猜答案。用 EXISTS PATH 记录“有没有这个键”，用 PATH 读取“键对应的值”，再按数据质量要求配置 ON EMPTY 与 ON ERROR，这套组合既清晰又容易扩展到导入、更新和审计流程。