MySQL JSON_TABLE 怎么把嵌套数组展开成行

如果订单明细放在 JSON 列里，想把 items 或更深一层的 options 变成可筛选的关系行，MySQL 里最直接的工具就是 JSON_TABLE()：根路径先确定父对象，NESTED PATH '$.items[*]' 把商品数组展开，再嵌套一层 NESTED PATH '$.options[*]' 处理子数组。

记住三条边界：路径前的表达式决定行源，NESTED PATH 决定数组层级，ON EMPTY 与 ON ERROR 分别处理“没有这个值”和“值无法按目标类型保存”。

根路径和第一层 NESTED PATH 决定行源

先假设表 sales_order 有 id 和 JSON 列 payload。一条 payload 的形状如下：订单号在对象顶层，商品位于 items 数组，商品里还可能有 options 数组。顶层是对象，所以根路径写 '$'，而不是把整个文档误当成数组。

SELECT
    o.id,
    jt.order_no,
    jt.item_no,
    jt.sku,
    jt.qty,
    jt.price
FROM sales_order AS o
JOIN JSON_TABLE(
    o.payload,
    '$' COLUMNS (
        order_no VARCHAR(32) PATH '$.order_no' ERROR ON EMPTY,
        NESTED PATH '$.items[*]' COLUMNS (
            item_no FOR ORDINALITY,
            sku VARCHAR(64) PATH '$.sku' ERROR ON EMPTY,
            qty DECIMAL(10, 2) PATH '$.qty' DEFAULT '0' ON ERROR,
            price DECIMAL(10, 2) PATH '$.price' NULL ON EMPTY
        )
    )
) AS jt ON TRUE
WHERE jt.sku IS NOT NULL;

这里的 '$' 只产生一个订单对象上下文；items[*] 匹配几个元素，就会产生几行。order_no 会在这些行中重复，这是父对象字段投影到子数组行的正常结果。FOR ORDINALITY 从 1 开始编号，适合保留商品在原数组中的位置。

第二层数组、缺失字段和类型错误要分开判断

如果每个商品还有多个选项，可以在商品的 COLUMNS 内再放一层 NESTED PATH。下面的结果是一行一个选项；同一个商品有两个选项，就会得到两行，商品字段会重复。

SELECT
    o.id,
    jt.order_no,
    jt.item_no,
    jt.option_no,
    jt.option_name,
    jt.option_value
FROM sales_order AS o
JOIN JSON_TABLE(
    o.payload,
    '$' COLUMNS (
        order_no VARCHAR(32) PATH '$.order_no',
        NESTED PATH '$.items[*]' COLUMNS (
            item_no FOR ORDINALITY,
            sku VARCHAR(64) PATH '$.sku',
            NESTED PATH '$.options[*]' COLUMNS (
                option_no FOR ORDINALITY,
                option_name VARCHAR(64) PATH '$.name' NULL ON EMPTY,
                option_value VARCHAR(128) PATH '$.value' NULL ON EMPTY
            )
        )
    )
) AS jt ON TRUE;

两层编号不要混为一谈：item_no 标识商品在 items 中的位置，option_no 标识该商品内部的选项位置。如果订单号和商品字段值可能重复，后续去重或回写时应保留这些序号，而不是只用文本值判断父子关系。

ON EMPTY 和 ON ERROR 解决的是两类风险

ON EMPTY 针对路径没有匹配值。例如某个商品没有 price，可以保留为 NULL，也可以写成 DEFAULT '0' ON EMPTY，或者用 ERROR ON EMPTY 拒绝这条数据。不要把“业务上没有价格”和“JSON 写错路径”都静默成 0，否则后续很难追查。

ON ERROR 针对匹配值无法按目标类型保存的情况，例如把数组 [1,2] 投影到 DECIMAL，或把不符合预期的文本转成数字。MySQL 会先提取 JSON 值，再按列类型转换；转换失败时按 ON ERROR 处理，数值截断还可能单独产生警告。

无子数组时保留父行还是过滤掉

NESTED PATH 没有匹配项时，MySQL 会产生一条子列为 NULL 的补行，这更接近外连接语义。因此上面的第一个查询使用 WHERE jt.sku IS NOT NULL，把没有商品的订单过滤掉；如果报表需要显示“订单存在但暂无商品”，就不要加这个过滤条件，而是在展示层解释空值。

落地前可以按下面的清单检查：根路径是否对应真实 JSON 类型；每层数组是否确实需要展开；父子序号是否保留；缺失字段和类型错误是否采用了不同策略；最后再决定是保留 NULL 补行还是在 WHERE 中模拟内连接。

常见问题

顶层 JSON 本身是数组时怎么写？

把根路径改为 '$[*]'，让数组中的每个对象成为一行源；列里的字段路径仍然相对于当前数组元素，例如 PATH '$.order_no'。

为什么子数组为空时仍然看到一行？

这是 NESTED PATH 的 NULL 补行行为。若只需要真正有子元素的记录，在外层使用子字段不为空的条件过滤；若要统计空数组订单，则保留这行。

为什么数字列没有报错却变成 NULL？

检查列定义是否使用了 NULL ON ERROR，以及原始值是否真的是可转换的标量。调试脏数据时可以临时改成 ERROR ON ERROR，让异常暴露出来，再决定生产策略。

官方手册对 JSON_TABLE() 的说明还包括 EXISTS PATH、多层 FOR ORDINALITY 和同级嵌套路径的行为。实际设计时先画出 JSON 层级，再逐层写 NESTED PATH，通常比一次性堆出复杂列定义更容易发现重复行和空值来源。