当前位置：首页 > 文章列表 > 数据库 > MySQL > MySQL JSON_TABLE 如何展开多层数组并保留父级字段

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

来源：17golang原创 2026-10-09 04:30:30 0浏览收藏

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

实践要点

NESTED PATH 的路径相对其父级行源计算，多层数组要按真实父子关系逐层写。
父级列与子级列放在同一个 JSON_TABLE 投影中，子数组展开时父级值会保留并重复。
同一 COLUMNS 下的兄弟 NESTED PATH 依次产出行，数量相加，不会自动形成笛卡尔积。

先把三级 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 基本就不会走偏。

MySQL JSON_TABLE 通过两层 NESTED PATH 展开订单商品批次并继承父级字段的结构图 — 图1：orders、items、batches 三层行源与父级字段继承关系。

用两层 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 会重复两次。这不是数据被重复写入，而是关系结果把父级上下文附着到了每个叶子行。

order_id	customer	item_no	sku	qty	batch_no	warehouse
101	张三	1	A-1	2	B01	华东
101	张三	1	A-1	2	B02	华南
101	张三	2	B-7	1	B09	华北

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

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

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

定义位置	字段示例	展开后的表现
订单层 COLUMNS	order_id、customer	对订单下所有商品和批次行重复
商品层 COLUMNS	item_no、sku、qty	对该商品下所有批次行重复
批次层 COLUMNS	batch_no、warehouse	每个匹配批次生成自己的值

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。总行数是各兄弟子句产生行数的和，不是乘积。

MySQL JSON_TABLE 层级嵌套与兄弟 NESTED PATH 行数语义对比图 — 图2：层级嵌套沿父子关系展开；兄弟子句分开产出，行数相加。

例如一个商品同时有 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。