MySQL JSON_TABLE 展开数组后怎样避免重复行

MySQL 的 JSON_TABLE() 展开数组后出现“重复行”，通常不是函数随机复制了数据，而是行路径、父表关联键和数组位置没有一起表达清楚。先把每一条展开行看成 父表主键 + 数组序号，再检查是否把多个 NESTED PATH 写成了不必要的组合，重复问题就能落到具体边界上。

官方文档入口：https://dev.mysql.com/doc/refman/8.4/en/json-table-functions.html。下面的 SQL 是说明性示例，输出是按语句推导的预期，不代表本文制作过程中在本机执行过。

为什么一展开就出现重复行

假设订单表有一个稳定的 order_id，JSON 中的 items 是商品数组。只要行路径使用 '$.items[*]'，数组中的每个元素都会成为一条关系行；两个订单即使商品名称相同，也不应该只靠商品名称判断重复。

SELECT o.order_id, jt.item_code, jt.item_pos
FROM orders AS o
JOIN JSON_TABLE(
    o.payload,
    '$.items[*]' COLUMNS (
        item_pos FOR ORDINALITY, -- 记录商品在当前订单数组中的位置
        item_code VARCHAR(64) PATH '$.code' -- 只读取商品编码
    )
) AS jt ON TRUE;

这里的逻辑标识是 (order_id, item_pos)，不是 item_code。如果两个数组元素的 code 恰好相同，它们仍可能是订单中的两行；如果两个订单都含有同一个编码，它们更不能被合并。先保留父键和序号，才能判断业务上的重复与展开造成的重复。

让行路径只匹配目标数组

JSON_TABLE() 的第二个参数是行路径，匹配到的每个 row source 都会贡献一行。排查时先把路径缩到真正的数组层级，再把字段路径写成相对于当前数组元素的路径。不要在父级使用过宽的通配符后，再期待列路径替你过滤掉多余对象。

SELECT o.order_id, jt.item_pos, jt.item_code, jt.qty
FROM orders AS o
JOIN JSON_TABLE(
    o.payload,
    '$.items[*]' COLUMNS (
        item_pos FOR ORDINALITY, -- 为每个订单的数组元素生成从1开始的序号
        item_code VARCHAR(64) PATH '$.code', -- 路径相对当前 item
        qty INT PATH '$.quantity' NULL ON EMPTY -- 缺字段时保留行并使用 NULL
    )
) AS jt ON TRUE;

这段写法的关键不是增加 DISTINCT，而是让一个数组元素只对应一个 row source。NULL ON EMPTY 处理的是字段缺失，不会把一个元素变成多行；如果数组本身没有匹配项，是否出现补充的 NULL 行还要结合 NESTED PATH 的语义单独判断。

我处理这类问题时会先暂时只选 order_id、item_pos 和原始元素字段。若这三列已经唯一，重复多半来自后续 JOIN；若序号本身成倍增加，就继续检查行路径或嵌套路径，而不是先用去重函数掩盖症状。

用父键加 FOR ORDINALITY 组成展开行标识

FOR ORDINALITY 会为当前 COLUMNS 产生的记录编号，初始值为1。它解决的是“值相同但位置不同”的区分问题，不是数据库表的永久主键。因此实际落表或回连业务表时，应把源表主键一起带出。

SELECT
    o.order_id,
    jt.item_pos,
    CONCAT(o.order_id, ':', jt.item_pos) AS item_key,
    jt.item_code
FROM orders AS o
JOIN JSON_TABLE(
    o.payload,
    '$.items[*]' COLUMNS (
        item_pos FOR ORDINALITY, -- 序号只在当前 JSON_TABLE 行源内有意义
        item_code VARCHAR(64) PATH '$.code' -- 允许相同编码按位置分别保留
    )
) AS jt ON TRUE;

若要把展开结果写回中间表，推荐使用两列 order_id、item_pos 作为复合键，或在数据模型中明确一个等价的行标识。不要把 item_code、商品名称这类业务值直接当唯一键，除非业务合同明确保证它们唯一。

多层数组怎样保持关联键稳定

当商品下还有批次数组时，每一行至少需要父数组序号和子数组序号。可以用多层 NESTED PATH 表达父子关系，也可以把不同数组拆成两次明确的展开。关键是不要让两个本应并列的数组共享一个不完整的键。

SELECT
    o.order_id,
    jt.item_pos,
    jt.batch_pos,
    jt.item_code,
    jt.batch_no
FROM orders AS o
JOIN JSON_TABLE(
    o.payload,
    '$.items[*]' COLUMNS (
        item_pos FOR ORDINALITY, -- 父数组的位置是关联键的一部分
        item_code VARCHAR(64) PATH '$.code',
        NESTED PATH '$.batches[*]' COLUMNS (
            batch_pos FOR ORDINALITY, -- 子数组位置不能与父序号混用
            batch_no VARCHAR(64) PATH '$.no'
        )
    )
) AS jt ON TRUE;

这时可以把 (order_id, item_pos, batch_pos) 视为展开层级的复合标识。官方语义里，嵌套路径相对它的父路径解析；而同一个 COLUMNS 中的 sibling NESTED PATH 是逐个产生记录，结果数是各自记录数之和，不是简单的笛卡尔积。把两个不同数组放在同一层并不等于“每个商品配每个标签”，业务上需要笛卡尔积时也要显式表达并重新设计键。

不要用 DISTINCT 掩盖路径或关联错误

DISTINCT 只按最终投影列去重。如果你漏选了父键和数组位置，它可能把本来属于不同订单的行合并；如果你选出了唯一的技术键，它又可能什么也去不掉。它适合业务确实要求“相同投影只保留一条”的场景，不适合修复行路径写错。

复查 JSON_TABLE 重复行的清单

• 源表是否有稳定的父记录主键，并且已经出现在选择列中。
• 行路径是否只匹配要展开的数组，列路径是否相对当前 row source。
• 每层数组是否有自己的 FOR ORDINALITY，复合键是否包含完整层级。
• 是否把多个 sibling NESTED PATH 误解成一一配对或乘法连接。
• 只有在业务允许合并时才使用 DISTINCT 或聚合，并记录合并依据。

一句话收口：先用 父键 + 各层数组序号 证明每行来自哪里，再决定是否需要业务去重。这样既不会误删真实记录，也能把行路径过宽、嵌套层级错误和后续 JOIN 重复区分开。

两个容易混淆的问题

数组元素内容完全相同，能不能自动合并？ 不能。JSON_TABLE 负责把匹配到的 row source 转成关系行，不会因为列值相同就替你判断业务重复。是否合并应由明确的业务键和规则决定。

只给 JSON_TABLE 增加 DISTINCT 可以吗？ 不建议把它当第一步。先保留父键、序号和必要的路径字段，确认重复的来源后再决定是否在最终业务投影上去重。