MySQL JSON_TABLE 怎样保留嵌套数组的父子关联

我在把订单 JSON 展平成明细行时，最容易踩的坑不是路径写错，而是展开以后不知道某个子项究竟属于哪个父对象。尤其当两个父对象的 parent_id 或名称恰好相同时，只把业务字段带出来是不够的。

用顶层 FOR ORDINALITY 标记父对象，用嵌套层的 FOR ORDINALITY 标记子对象，再把稳定业务键一起投影出来，就能在 JSON_TABLE() 展平后保留父子关联。缺少子数组时，MySQL 默认保留父行并把嵌套列补成 NULL。

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

本文只讨论 MySQL 8.4 中 JSON_TABLE() 的嵌套数组展开，不把问题扩展到其他 JSON 谓词。示例中的 top_ord 和 child_ord 是本次展开产生的定位信息；如果 JSON 本身有稳定的业务 ID，也应该同时保留。

先把父数组和子数组放进同一个 JSON_TABLE

假设一份文档里有两个父对象，每个父对象都有一个 items 子数组。为了故意制造容易混淆的场景，两个父对象的 parent_id 和 parent_name 相同，但它们代表不同的 JSON 对象。

SET @doc = '[
  {"parent_id":"A","parent_name":"同名订单","items":[
    {"sku":"P1","qty":2}, {"sku":"P2","qty":1}
  ]},
  {"parent_id":"A","parent_name":"同名订单","items":[
    {"sku":"P3","qty":4}
  ]}
]';

SELECT jt.top_ord, jt.parent_id, jt.parent_name,
       jt.child_ord, jt.sku, jt.qty
FROM JSON_TABLE(
  @doc,
  '$[*]' COLUMNS (
    top_ord FOR ORDINALITY,
    parent_id VARCHAR(32) PATH '$.parent_id',
    parent_name VARCHAR(64) PATH '$.parent_name',
    NESTED PATH '$.items[*]' COLUMNS (
      child_ord FOR ORDINALITY,
      sku VARCHAR(32) PATH '$.sku',
      qty INT PATH '$.qty'
    )
  )
) AS jt;

这里的外层路径 $[*] 为每个父对象建立一个行来源，parent_id 和 parent_name 直接从当前父对象取值。NESTED PATH '$.items[*]' 再以当前父对象为相对路径，展开它的每个子项。于是同一个父对象的字段会出现在它的每一条子行上。

为什么父子两层都要有 FOR ORDINALITY

只保留 parent_id 看起来很自然，但示例里两个父对象都叫 A。如果再只依赖 parent_name，第一组的 P1、P2 和第二组的 P3 就无法可靠地回指到原始父对象。

top_ord 由顶层 COLUMNS 生成，表示当前父行在顶层数组中的序号；嵌套 COLUMNS 里的 child_ord 表示当前子项在该父对象子数组中的序号。两层序号的组合可以写成 (top_ord, child_ord)，它表达的是“第几个父对象下的第几个子对象”。

这里有一个取舍：序号只对本次 JSON 展开结果稳定，并不等同于业务主键。写回关系表或跨批次合并时，优先使用 JSON 中真实存在的订单号、明细号等稳定键；把序号作为定位辅助列，排查重复值和回溯原始数组位置。

把父子定位信息显式组合出来

如果下游要把结果交给 ETL 或应用层，可以在查询里直接生成可读的定位字段。不要用字符串拼接替代真正的业务键，它只适合在当前结果集中追踪层级。

SELECT
  jt.top_ord,
  jt.child_ord,
  jt.parent_id,
  jt.sku,
  CONCAT(jt.top_ord, ':', jt.child_ord) AS json_position
FROM JSON_TABLE(
  @doc,
  '$[*]' COLUMNS (
    top_ord FOR ORDINALITY,
    parent_id VARCHAR(32) PATH '$.parent_id',
    NESTED PATH '$.items[*]' COLUMNS (
      child_ord FOR ORDINALITY,
      sku VARCHAR(32) PATH '$.sku'
    )
  )
) AS jt;

json_position 便于日志、导入错误表或调试时快速定位，但它的含义依赖原始数组顺序。如果上游会重新排序数组，不能拿它当跨版本不变的主键。

子数组缺失时，为什么还能看到父行

NESTED PATH 没有匹配项时，MySQL 默认会保留外层父行，并把这个嵌套路径下的列补成 NULL。这相当于外连接效果，适合回答“哪些父对象没有明细”。

SELECT jt.top_ord, jt.parent_id, jt.child_ord, jt.sku
FROM JSON_TABLE(
  '[
    {"parent_id":"A","items":[{"sku":"P1"}]},
    {"parent_id":"B"}
  ]',
  '$[*]' COLUMNS (
    top_ord FOR ORDINALITY,
    parent_id VARCHAR(32) PATH '$.parent_id',
    NESTED PATH '$.items[*]' COLUMNS (
      child_ord FOR ORDINALITY,
      sku VARCHAR(32) PATH '$.sku'
    )
  )
) AS jt;

如果业务只需要确实存在子项的行，再在外层加 WHERE jt.sku IS NOT NULL。这个过滤是查询语义上的内连接效果，不要为了“消除空值”而把父层的定位列一起删掉。

两个 NESTED PATH 不会自动形成笛卡尔积

同一父层里放两个兄弟 NESTED PATH 时，MySQL 会依次处理它们；一个嵌套路径产出记录时，另一个嵌套路径的列会补成 NULL。所以最终行数是各路径产出行数之和，而不是两个数组长度相乘。

如果目标是保留一个明确的父子链，应把真正的子数组写在另一个 NESTED PATH 内部，并为每一层保留自己的序号。不要把兄弟路径的结果误读成同一条明细，也不要用普通的行号去猜两个数组之间的配对关系。

最后用这张表检查关联是否完整

实践中我会同时保留业务键和两层序号：业务键负责长期关联，序号负责解释“这条结果来自 JSON 的哪一层、哪一个数组元素”。这样即使字段值重复，展开后的结果也仍然能够回到原始父子结构。

常见问题

父字段为什么会在多条子行重复？ 因为每个子元素都会生成一行，父层路径列会随这条行一起投影；这不是重复数据错误。

只有一个 FOR ORDINALITY 可以吗？ 如果只需要父层定位可以，但嵌套数组里还要区分同一父对象下的多个子对象时，应在子层再定义一个序号。

能不能只用 parent_id 关联？ 只有当它在文档内真正唯一且稳定时才可以。示例故意让两个父对象共享同一个值，正是为了说明为什么还要保留层级定位。