JSON_TABLE 怎样展开嵌套文档

我第一次用 JSON_TABLE() 展开订单文档时，最容易误判的是“一个订单有几个结果行”。真正决定行数的不是 JSON 看起来有几层，而是根路径匹配了多少父级元素、每个 NESTED PATH 匹配了多少子元素，以及缺失数组时 MySQL 是否保留一行。

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

把根数组当作父级行，把子数组放进相对的 NESTED PATH，再用 FOR ORDINALITY 保留层级身份。这样展开后的行数和 NULL 都有明确来源，后面的过滤才不会把数据完整性问题藏起来。

先把根数组映射成父级行

假设一列保存了订单文档，每个订单有 id、customer 和 items 数组。JSON_TABLE() 的第二个参数是行路径：路径匹配到几个 JSON 元素，外层就先建立几行。外层 COLUMNS 再从每个父元素读取标量字段。

-- 根路径 $[*] 把文档数组中的每个订单对象作为一个父级行源。
SELECT jt.order_id, jt.customer
FROM JSON_TABLE(
    '[
      {"id": 101, "customer": "林杉", "items": [{"sku": "A-1", "qty": 2}]},
      {"id": 102, "customer": "周宁", "items": [{"sku": "B-2", "qty": 1}]}
    ]',
    '$[*]' COLUMNS (
        order_id INT PATH '$.id',
        customer VARCHAR(32) PATH '$.customer'
    )
) AS jt;

这里的 $[*] 是父级 row source，$.id 和 $.customer 是相对于当前订单对象的字段路径。路径匹配一个元素就形成一行，匹配两个元素就形成两行；不要把 COLUMNS 中的字段数理解成结果行数。

用相对 NESTED PATH 展开子数组

子数组要放进父级 COLUMNS 内的 NESTED PATH。它的路径相对于当前父级 row source，所以写成 $.items[*]，而不是重新从整份文档写一条绝对路径。每个匹配到的商品对象都会生成一行，同时带上父级订单字段。

-- NESTED PATH 只展开当前订单的 items，父级字段会随子级行保留。
SELECT jt.order_id, jt.customer, jt.sku, jt.qty
FROM JSON_TABLE(
    '[
      {"id": 101, "customer": "林杉", "items": [{"sku": "A-1", "qty": 2}, {"sku": "A-2", "qty": 3}]},
      {"id": 102, "customer": "周宁", "items": [{"sku": "B-2", "qty": 1}]}
    ]',
    '$[*]' COLUMNS (
        order_id INT PATH '$.id',
        customer VARCHAR(32) PATH '$.customer',
        NESTED PATH '$.items[*]' COLUMNS (
            sku VARCHAR(32) PATH '$.sku',
            qty INT PATH '$.qty'
        )
    )
) AS jt;

这份数据对应三条商品行：订单 101 的父级字段重复两次，订单 102 的父级字段出现一次。重复不是异常，而是父子关系展开后的自然结果。图1把“订单文档—根数组—父级字段—相对子路径—结果行”放在同一张结构图里，适合用来检查路径到底相对于哪一层。

缺少子数组时，为什么仍可能有一行

我在处理历史订单时遇到过没有 items 字段的文档。MySQL 对没有匹配到的嵌套路径会生成一条 NULL 补全行，父级字段仍然保留，嵌套列为 NULL。这更接近外连接语义，不等于“该订单有一个空商品”。

-- 没有 items 的父级仍保留一行；需要商品时再显式过滤 NULL。
SELECT jt.order_id, jt.sku
FROM JSON_TABLE(
    '[
      {"id": 101, "items": [{"sku": "A-1"}]},
      {"id": 103, "items": []},
      {"id": 104}
    ]',
    '$[*]' COLUMNS (
        order_id INT PATH '$.id',
        NESTED PATH '$.items[*]' COLUMNS (
            sku VARCHAR(32) PATH '$.sku'
        )
    )
) AS jt;

-- 只要真正有商品的订单时，使用 WHERE 把 NULL 补全行排除。
SELECT jt.order_id, jt.sku
FROM JSON_TABLE(
    '[{"id": 101, "items": [{"sku": "A-1"}]}, {"id": 104}]',
    '$[*]' COLUMNS (
        order_id INT PATH '$.id',
        NESTED PATH '$.items[*]' COLUMNS (
            sku VARCHAR(32) PATH '$.sku'
        )
    )
) AS jt
WHERE jt.sku IS NOT NULL;

这里要区分两件事：空数组通常没有子元素可展开，而缺少路径会触发嵌套列的 NULL 补全语义。业务若需要统计“没有商品的订单”，保留这行并检查 sku IS NULL；业务若只想读取商品明细，再明确使用 IS NOT NULL 过滤。不要在外层随手加过滤后，又把结果行数当成原始订单数。

多层嵌套时，路径始终相对当前父级

如果 items 里的商品还有 tags 数组，可以继续嵌套。第二层路径相对于当前商品对象写成 $.tags[*]。每层都应只读取当前对象的字段，这样同名字段不会因为路径写错而从其他层串进来。

-- 第二层 NESTED PATH 相对于当前商品对象，而不是相对于整份文档。
SELECT jt.order_id, jt.sku, jt.tag
FROM JSON_TABLE(
    '[
      {"id": 101, "items": [
        {"sku": "A-1", "tags": ["礼盒", "易碎"]}
      ]}
    ]',
    '$[*]' COLUMNS (
        order_id INT PATH '$.id',
        NESTED PATH '$.items[*]' COLUMNS (
            sku VARCHAR(32) PATH '$.sku',
            NESTED PATH '$.tags[*]' COLUMNS (
                tag VARCHAR(32) PATH '$'
            )
        )
    )
) AS jt;

多层展开时，行数会随着子数组基数增长。图1中的“相对路径”是一个很实用的检查点：看到 $.tags[*] 时，先问它当前绑定的是商品对象还是订单对象。不要用一个看似能匹配的全局路径掩盖层级关系。

用 FOR ORDINALITY 保留重复父对象的层级

当两个父对象的 customer、sku 甚至其他业务字段都一样时，仅靠字段值无法判断子数组属于哪个父对象。FOR ORDINALITY 会为当前 COLUMNS 产生的记录编号，父层和子层各放一个序号，就能保留层级身份。

-- 两个父对象的业务值可以相同，用 top_ord 和 item_ord 保留所属关系。
SELECT jt.top_ord, jt.item_ord, jt.customer, jt.sku
FROM JSON_TABLE(
    '[
      {"customer": "同名客户", "items": [{"sku": "同款"}]},
      {"customer": "同名客户", "items": [{"sku": "同款"}, {"sku": "另一款"}]}
    ]',
    '$[*]' COLUMNS (
        top_ord FOR ORDINALITY,
        customer VARCHAR(32) PATH '$.customer',
        NESTED PATH '$.items[*]' COLUMNS (
            item_ord FOR ORDINALITY,
            sku VARCHAR(32) PATH '$.sku'
        )
    )
) AS jt;

top_ord 能区分根数组里的第几个父对象，item_ord 能区分该父对象内的第几个商品。它们不是业务主键，也不应被写回数据库当永久 ID；它们的价值是帮助排查展开结果、建立临时关联和复核父子归属。

兄弟 NESTED PATH 不是笛卡尔相乘

这是我最容易在第一次设计查询时想错的地方：同一个 COLUMNS 中有两条兄弟 NESTED PATH 时，MySQL 会依次处理它们，记录数是各自产生的记录数相加，另一条路径的列在当前部分通常为 NULL，不是两组数组长度相乘。

-- 两条兄弟路径分别产生记录；不要把结果理解为 tags 与 coupons 的笛卡尔积。
SELECT jt.order_id, jt.tag, jt.coupon
FROM JSON_TABLE(
    '[{"id": 101, "tags": ["礼盒", "易碎"], "coupons": ["满减"]}]',
    '$[*]' COLUMNS (
        order_id INT PATH '$.id',
        NESTED PATH '$.tags[*]' COLUMNS (
            tag VARCHAR(32) PATH '$'
        ),
        NESTED PATH '$.coupons[*]' COLUMNS (
            coupon VARCHAR(32) PATH '$'
        )
    )
) AS jt;

这个父对象有两个标签、一个优惠券时，兄弟路径的结果语义是两条标签记录加一条优惠券记录，而不是两乘一后再复制成一组“标签—优惠券”配对。如果业务真的需要配对关系，应先确认 JSON 数据是否表达了配对关系，再设计明确的关联键或分别展开后连接；不能靠把两条 NESTED PATH 写在一起猜出配对。

用数据完整性清单收尾

如果只是把嵌套 JSON 读成报表，推荐先写一条只包含根路径和父级字段的查询，再加入一层 NESTED PATH，最后才处理多层数组、空值和兄弟路径。每加一层都单独核对行数来源，这比看到结果不对后一次性修改多条路径更容易定位。

JSON_TABLE 的 NESTED PATH 能直接写绝对路径吗？

嵌套路径的语义是相对于父级路径。要表达当前父对象中的子数组，应使用类似 $.items[*] 的相对路径，不能把它当成从文档根重新开始的独立查询。

没有子数组时怎样保留父级数据？

保留默认的 NULL 补全语义即可；如果只要真实子项，再在外层根据子列是否为 NULL 过滤。两种写法对应不同的数据目标。

两个 NESTED PATH 为什么没有生成配对行？

兄弟嵌套路径按各自记录集合依次产生结果，记录数是相加关系。需要配对时，必须在数据模型或后续查询中提供明确的关联依据。

JSON_TABLE 的核心不是把 JSON“摊平”得越多越好，而是让每一行都有可解释的父级来源、子级来源和空值来源。根路径决定父行，NESTED PATH 决定子行，FOR ORDINALITY 保留重复层级，兄弟路径则提醒我们不要凭直觉猜行数。