MySQL JSON_TABLE 如何展开多层数组并保留父级字段

一个 JSON 文档里同时放着订单、商品和批次数组时，最容易写错的不是字段路径，而是层级关系。正确做法是把最外层数组交给 JSON_TABLE() 的行路径，再在对应的 COLUMNS 中逐层嵌套 NESTED PATH。订单号、客户名、商品编码这类父级标量列会自动复制到每一条更深层的结果行，不需要额外回连 JSON。

先把三级 JSON 映射成三个行源

假设业务表保存一个订单文档：最外层是 orders[*]，每个订单内有 items[*]，每个商品内还有 batches[*]。这三个数组不能都从根路径直接找，而要依次相对父级展开。

下面的 JSON 是完整测试数据。JSON 语法本身不支持注释，因此字段含义紧接在代码后说明。

{
  "orders": [
    {
      "order_id": 101,
      "customer": "张三",
      "items": [
        {
          "sku": "A-1",
          "qty": 2,
          "batches": [
            {"batch_no": "B01", "warehouse": "华东"},
            {"batch_no": "B02", "warehouse": "华南"}
          ]
        },
        {
          "sku": "B-7",
          "qty": 1,
          "batches": [
            {"batch_no": "B09", "warehouse": "华北"}
          ]
        }
      ]
    }
  ]
}

order_id 与 customer 属于订单层，sku 与 qty 属于商品层，batch_no 与 warehouse 属于批次层。先把这个边界划清，SQL 基本就不会走偏。

用两层 NESTED PATH 一次展开订单、商品和批次

先创建一张只含主键和 JSON 文档的测试表，再把三级数据写进去。生产环境可以直接把 p.doc 换成真实 JSON 列。

-- 创建最小测试表，避免其他字段干扰观察结果
CREATE TABLE order_payloads (
  id BIGINT PRIMARY KEY,
  doc JSON NOT NULL
);

-- 写入一份订单、商品、批次三级结构的测试文档
INSERT INTO order_payloads (id, doc) VALUES (
  1,
  '{"orders":[{"order_id":101,"customer":"张三","items":[{"sku":"A-1","qty":2,"batches":[{"batch_no":"B01","warehouse":"华东"},{"batch_no":"B02","warehouse":"华南"}]},{"sku":"B-7","qty":1,"batches":[{"batch_no":"B09","warehouse":"华北"}]}]}]}'
);

核心查询只有一个原则：每一层 NESTED PATH 都写在父层的 COLUMNS 里面。MySQL 会把当前匹配项作为下一层的行源。

-- 从订单数组开始，每个 orders[*] 元素先生成一条父级行
SELECT
  p.id AS payload_id,
  jt.order_id,
  jt.customer,
  jt.item_no,
  jt.sku,
  jt.qty,
  jt.batch_no,
  jt.warehouse
FROM order_payloads AS p
JOIN JSON_TABLE(
  p.doc,
  '$.orders[*]' COLUMNS (
    -- 订单层标量列会复制到更深层生成的结果行
    order_id BIGINT PATH '$.order_id',
    customer VARCHAR(50) PATH '$.customer',
    NESTED PATH '$.items[*]' COLUMNS (
      -- 商品序号在每个订单的 items 数组内从 1 开始
      item_no FOR ORDINALITY,
      sku VARCHAR(32) PATH '$.sku',
      qty INT PATH '$.qty',
      NESTED PATH '$.batches[*]' COLUMNS (
        -- 批次路径相对当前商品对象，而不是相对 JSON 根节点
        batch_no VARCHAR(32) PATH '$.batch_no',
        warehouse VARCHAR(32) PATH '$.warehouse'
      )
    )
  )
) AS jt ON TRUE;

这个查询会得到三行：商品 A-1 对应 B01、B02 两个批次，商品 B-7 对应 B09 一个批次。订单号 101 和客户“张三”会出现在三行中；A-1 的数量 2 会重复两次。这不是数据被重复写入，而是关系结果把父级上下文附着到了每个叶子行。

父级字段为什么能保留下来

MySQL 8.4 手册把 NESTED PATH 描述为：把嵌套对象或数组展平成行，并把父对象或父数组中的 JSON 值放在同一行。路径的基准也会逐层变化：顶层路径相对输入文档，第一层 NESTED PATH 相对订单行源，第二层则相对商品行源。

因此，父级字段是否保留，取决于它是否在正确的作用域里被定义，而不是取决于 SELECT 是否再次提取它。可以按下面的速查关系理解：

FOR ORDINALITY 用来辨认同值父记录

如果多个父对象的业务字段恰好相同，只看 sku 或名称可能无法判断叶子行来自哪个数组元素。FOR ORDINALITY 会在当前 COLUMNS 作用域内从 1 计数，适合补一列结构序号。

-- 同时记录订单、商品和批次在各自数组中的位置
SELECT jt.order_no, jt.item_no, jt.batch_no_in_item,
       jt.order_id, jt.sku, jt.batch_code
FROM order_payloads AS p
JOIN JSON_TABLE(
  p.doc,
  '$.orders[*]' COLUMNS (
    order_no FOR ORDINALITY,
    order_id BIGINT PATH '$.order_id',
    NESTED PATH '$.items[*]' COLUMNS (
      item_no FOR ORDINALITY,
      sku VARCHAR(32) PATH '$.sku',
      NESTED PATH '$.batches[*]' COLUMNS (
        batch_no_in_item FOR ORDINALITY,
        batch_code VARCHAR(32) PATH '$.batch_no'
      )
    )
  )
) AS jt ON TRUE;

序号只表示当前 JSON 数组中的位置，不应该直接当作长期稳定的业务主键。真正需要回写或去重时，仍应使用订单 ID、商品 ID、批次 ID 等业务标识。

兄弟 NESTED PATH 不会自动形成笛卡尔积

另一个常见误区，是把同一对象下两个数组写成同级兄弟 NESTED PATH，然后期待两组元素两两组合。MySQL 的实际语义不是这样：同一个 COLUMNS 里的兄弟子句会依次处理，一边产出行时，另一边的列为 NULL。总行数是各兄弟子句产生行数的和，不是乘积。

例如一个商品同时有 sizes[*] 和 colors[*]，分别包含 2 个和 3 个元素。把它们写成兄弟子句会得到 5 行，而不是 6 种组合。如果业务确实需要尺寸与颜色的组合，应先确认 JSON 是否表达了这种关系，再显式使用两次 JSON_TABLE() 或关系表连接，不能依赖兄弟子句隐式相乘。

空数组和缺失字段怎么处理

当某个 NESTED PATH 没有匹配项时，MySQL 会保留父级行，并把该嵌套层的列补成 NULL。这相当于父级与嵌套层之间的外连接语义。是否保留这类行，要根据业务目标决定。

-- 只保留真正存在批次的叶子行，相当于把最深层改成内连接效果
SELECT jt.order_id, jt.sku, jt.batch_no
FROM order_payloads AS p
JOIN JSON_TABLE(
  p.doc,
  '$.orders[*]' COLUMNS (
    order_id BIGINT PATH '$.order_id',
    NESTED PATH '$.items[*]' COLUMNS (
      sku VARCHAR(32) PATH '$.sku',
      NESTED PATH '$.batches[*]' COLUMNS (
        batch_no VARCHAR(32) PATH '$.batch_no'
      )
    )
  )
) AS jt ON TRUE
WHERE jt.batch_no IS NOT NULL;

如果需要区分“字段不存在”和“字段存在但值为空”，可以增加 EXISTS PATH 列。标量缺失时，普通 PATH 列默认使用 NULL ON EMPTY；只有业务明确要求遇到缺失立即失败时，才考虑 ERROR ON EMPTY。

生产查询先估算展开后的行数

JSON_TABLE() 解决的是投影和展开，不会替你控制数据规模。一条订单包含 20 个商品，每个商品 10 个批次，单个文档就可能产出 200 行。文档数量再放大后，排序、聚合和后续连接的成本会迅速上升。

• 先在来源表上用主键、时间范围、租户或状态缩小文档集合，再调用 JSON_TABLE()。
• 只投影查询真正需要的列，并为每列选择合适的数据类型，避免无意义的大字符串转换。
• 在测试环境统计订单、商品、批次数组的最大值与常见值，按层级基数乘积估算最坏行数。
• 如果查询长期依赖同一批字段做过滤、连接和聚合，考虑把稳定业务实体规范化到关系表，而不是每次在线展开整个 JSON。

常见问题

NESTED PATH 可以嵌套多少层？

语法允许继续嵌套，但层数越深，路径维护和结果行数越难控制。工程上应以真实数据层级为准，并提前测量最坏展开规模。

为什么父级字段看起来被重复了？

因为每个叶子元素都要成为独立关系行，父级值必须随行携带。这是正常投影结果，不代表源 JSON 中产生了重复数据。

空数组为什么仍然出现一行 NULL？

NESTED PATH 没有匹配时会生成 NULL 补行，以保留父级。若只要有实际子元素的行，在外层查询增加对应子列的非空条件。

两个兄弟数组如何做全组合？

不要指望兄弟 NESTED PATH 自动相乘。应分别展开两个数组，再用明确的连接条件组合；如果没有自然连接条件，先确认业务是否真的需要笛卡尔积。

JSON_TABLE 的别名可以省略吗？

不可以。它作为表函数出现在 FROM 中，必须提供表别名，例如示例中的 AS jt。

把复杂 JSON 当成分层行源来读

多层数组展开的关键不在于堆更多 JSON 路径，而在于让每一级路径待在正确的父级作用域。先画出 orders[*] → items[*] → batches[*] 的父子关系，再把标量列放回所属层级，父级字段就会自然保留。最后补上序号、空数组策略和行数预算，这条查询才适合从演示走向真实业务。