MySQL JSON_TABLE 展开嵌套数组的列定义与空值处理

把订单、日志或配置放在 JSON 列后，真正麻烦的通常不是取出一个字段，而是把多层数组稳定地摊平成关系行。MySQL 的 JSON_TABLE() 适合做这件事：顶层路径负责生成父行，NESTED PATH 负责把子数组展开，缺失值和类型转换则交给列级的 ON EMPTY、ON ERROR。

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

用 NESTED PATH 把父对象和明细数组映射成行

假设每条记录的 payload 类似 {"order_id":101,"customer":"林川","items":[{"sku":"A-1","qty":2}]}。顶层使用 '$[*]' 逐个匹配父对象，子层再用 '$.items[*]' 取明细。列路径相对当前层级计算，父列会在每个子明细行上重复，这是后续关联订单号的关键。

SELECT jt.top_ord, jt.item_ord, jt.order_id, jt.customer, jt.sku, jt.qty
FROM JSON_TABLE(
  '[{"order_id":101,"customer":"林川","items":[{"sku":"A-1","qty":2},{"sku":"B-7","qty":1}]}]',
  '$[*]' COLUMNS (
    top_ord FOR ORDINALITY, -- 标识顶层父对象，避免同值对象难以区分
    order_id INT PATH '$.order_id', -- 从父对象读取订单号
    customer VARCHAR(32) PATH '$.customer', -- 保留父对象字段
    NESTED PATH '$.items[*]' COLUMNS (
      item_ord FOR ORDINALITY, -- 标识同一订单中的明细位置
      sku VARCHAR(32) PATH '$.sku', -- 读取商品编码
      qty INT PATH '$.qty' -- 读取数量并转换为整数
    )
  )
) AS jt;

这里的 FOR ORDINALITY 不是业务主键，它只是当前 COLUMNS 层生成的行号。需要把结果写回明细表时，仍应使用订单号、明细 ID 等真实键。

空数组、缺失字段与类型错误要分开处理

三个现象看起来都像“没有值”，但处理点不同。路径不存在时触发 ON EMPTY；JSON 值是对象或数组、不能转换到目标标量类型时触发 ON ERROR；JSON null 在 MySQL 8.4 的 JSON_TABLE 中对应 SQL NULL。默认行为通常是返回 NULL，生产查询建议按字段重要性显式写出规则。

SELECT jt.order_id, jt.sku, jt.has_discount
FROM JSON_TABLE(
  '[{"order_id":101,"items":[{"sku":"A-1","discount":null}]},{"order_id":102}]',
  '$[*]' COLUMNS (
    order_id INT PATH '$.order_id', -- 父字段缺失时仍要能定位父行
    NESTED PATH '$.items[*]' COLUMNS (
      sku VARCHAR(32) PATH '$.sku' ERROR ON EMPTY, -- 明细没有编码就让数据暴露
      has_discount INT EXISTS PATH '$.discount' -- 区分字段不存在与字段值为 NULL
    )
  )
) AS jt;

如果 items 不存在或为空，NESTED PATH 会产生保留父行的 NULL 补足行。想只保留确实有明细的记录，可以在外层使用 WHERE jt.sku IS NOT NULL。这一步表达的是过滤策略，不是让 JSON_TABLE 变成了内连接。

兄弟 NESTED PATH 不等于笛卡尔积

同一个 COLUMNS 中出现多个兄弟 NESTED PATH 时，MySQL 会先后处理它们，另一组子列在当前记录中补 NULL，行数是各组产生行数之和，不是相乘。如果业务需要两个数组逐项配对，应先明确配对键或序号，再分别展开后关联，不能仅靠把两个 NESTED PATH 写在一起猜出对应关系。

落地前可以按这张清单检查：顶层路径是否会生成父行；子路径是否相对正确；缺失字段是否用 ON EMPTY 说明；类型转换是否配置 ON ERROR；过滤 NULL 是否真的符合业务的内连接语义；兄弟数组是否被误当成笛卡尔积。

常见问题

JSON_TABLE 的别名可以省略吗？

不建议也不能省略。它返回的是派生表，必须使用别名，例如 AS jt，外层才能引用列。

空数组为什么还会出现一行？

NESTED PATH 没有匹配时会生成 NULL 补足行，这是保留父对象的外连接式语义；用 WHERE 子列 IS NOT NULL 可筛掉它。

什么时候该用 EXISTS PATH？

当你要区分“字段不存在”和“字段存在但值为 JSON null”时使用。它返回存在标记，不负责把字段值转换成业务类型。