MySQL JSON_TABLE为缺失字段提供默认值的映射方法

把订单 JSON 数组拆成关系结果时，最容易踩的坑不是路径语法，而是“字段没出现”之后该返回什么。MySQL 的 JSON_TABLE() 可以在列定义中使用 DEFAULT ... ON EMPTY，为缺失路径填充默认值；再用 FOR ORDINALITY 保留数组序号、用 EXISTS PATH 标记字段是否出现，最终结果既能落库，也能回溯原始数据。

实用组合是：父表主键负责追溯来源，FOR ORDINALITY 负责区分数组行，DEFAULT ON EMPTY 负责缺失字段，EXISTS PATH 负责保留“是否出现”的事实；不要把 JSON null 和字段缺失混成同一种状态。

先保留订单主键，再展开 items 数组

假设 order_payload 保存订单主键 order_id 和 JSON 列 payload。数组展开后，单独的 item_no 不能替代父级主键，写入临时表或明细表时应把两者一起保留。

-- 把每个订单的 items 数组展开成明细行，并保留来源键
SELECT p.order_id,
       jt.item_no,
       jt.sku,
       jt.qty,
       jt.has_discount
FROM order_payload AS p
JOIN JSON_TABLE(
  p.payload,
  '$.items[*]' COLUMNS (
    item_no FOR ORDINALITY,
    sku VARCHAR(32) PATH '$.sku' DEFAULT '"UNKNOWN"' ON EMPTY,
    qty INT PATH '$.qty' DEFAULT '1' ON EMPTY DEFAULT '0' ON ERROR,
    has_discount TINYINT EXISTS PATH '$.discount'
  )
) AS jt ON TRUE;

这里的 $.items[*] 让数组元素成为行来源；order_id + item_no 可以构成一次展开中的稳定定位键。ON ERROR 写在 ON EMPTY 后面，既符合当前语法习惯，也方便读者区分“没有值”和“值无法转换”。

DEFAULT ON EMPTY 的写法与类型

DEFAULT json_string ON EMPTY 只在路径没有匹配数据时生效。默认值会先按 JSON 字面量解析，再按照列类型转换，所以数字列可以写 '1'，字符串列要使用合法的 JSON 字符串字面量，例如 '"UNKNOWN"'。默认值的类型、长度和精度仍受目标列约束。

如果业务要求缺少 SKU 就暴露问题，可以写成 ERROR ON EMPTY，而数量这类可兜底字段才使用默认值。不要把所有字段都默认成空字符串，否则后续很难区分“供应方没传”和“供应方确实传了空值”。

把缺失、JSON null 和类型异常分开

这三种状态需要分别记录。路径不存在时触发 ON EMPTY；路径存在但值是 JSON null 时，结果是 SQL NULL；对象或数组被写入标量列、或者发生类型转换问题时，才进入 ON ERROR。需要保留“字段是否出现”时，增加一个 EXISTS PATH 列即可，它返回 1 或 0，不会把默认值误当成原始事实。

-- 用存在性标记辅助排查：默认值不等于原始字段已出现
SELECT jt.sku,
       jt.sku_present,
       jt.qty
FROM JSON_TABLE(
  '[{"sku":"A-01"},{"sku":null},{"qty":3}]',
  '$[*]' COLUMNS (
    sku VARCHAR(32) PATH '$.sku' DEFAULT '"UNKNOWN"' ON EMPTY,
    sku_present TINYINT EXISTS PATH '$.sku',
    qty INT PATH '$.qty' DEFAULT '1' ON EMPTY DEFAULT '0' ON ERROR
  )
) AS jt;

这样，第三行的 sku_present=0 表示没有 SKU 路径；第二行的 sku_present=1 且 sku IS NULL 则表示路径出现但值为 JSON null。若要把类型异常当作脏数据拒绝，使用 ERROR ON ERROR，不要只依赖默认值掩盖问题。

落库前的核对清单

准备把展开结果写入明细表时，按四项核对：第一，父表主键是否始终随行输出；第二，数组序号是否只用于本次 JSON 文档，不能冒充全局明细 ID；第三，默认值是否与目标列类型、精度和业务含义一致；第四，是否保留了 EXISTS PATH 或原始 JSON，方便追查供应方数据。

如果还要展开子数组，使用 NESTED PATH 时要注意无匹配会产生补全行，相关列可能为 NULL。先明确是保留父行还是过滤空子项，再决定是否在外层用 WHERE 过滤。对于严格导入，建议先写入暂存表，统计默认值命中数和转换警告，再进入正式明细表。

相关问题

默认值为什么没有生效？先确认路径确实是“没有匹配”，而不是 JSON null、对象类型或路径写错；路径写错本身仍可能导致表达式错误，不能用默认值代替路径校验。

能不能只用 COALESCE？可以处理已经得到的 SQL NULL，但它无法告诉你 NULL 来自字段缺失、JSON null 还是上游转换结果；需要审计来源时，应同时保留 EXISTS PATH 或原始文档。