MySQL JSON_TABLE 展开教程：空数组订单保留与准确聚合

下面这类订单报表很容易少算：先把 JSON 明细数组展开，再按订单聚合。含两条明细的订单正常出现，没有明细的订单却整张消失。把 SUM 外面包上 COALESCE 仍然没用，因为父记录在进入聚合之前就已经被连接方式丢掉了。

要修的是行的保留规则，而不是总和显示成零的方式。MySQL 8.4 的 JSON_TABLE 可以把文档转成行集，父表是否保留空数组记录，则取决于你怎样连接这个行集。

先跑一条会漏订单的查询

在独立实验库准备五张订单。其中既有正常数组，也有空数组、缺失数组，以及数量为空或类型错误的明细。

CREATE DATABASE json_expand_lab;
USE json_expand_lab;
CREATE TABLE sales_order (
    id BIGINT NOT NULL PRIMARY KEY,
    tenant_id INT NOT NULL,
    detail JSON NOT NULL,
    KEY idx_tenant (tenant_id, id)
) ENGINE=InnoDB;

INSERT INTO sales_order VALUES
(1, 7, '{"items":[{"sku":"A","qty":2},{"sku":"B","qty":1}]}'),
(2, 7, '{"items":[]}'),
(3, 7, '{}'),
(4, 7, '{"items":[{"sku":"C","qty":null}]}'),
(5, 7, '{"items":[{"sku":"D","qty":"oops"}]}');

先只展开 SKU，避免类型错误掩盖连接问题：

SELECT o.id, j.line_no, j.sku
FROM sales_order AS o
CROSS JOIN JSON_TABLE(
    o.detail, '$.items[*]'
    COLUMNS (
        line_no FOR ORDINALITY,
        sku VARCHAR(30) PATH '$.sku' NULL ON EMPTY ERROR ON ERROR
    )
) AS j
WHERE o.tenant_id = 7
ORDER BY o.id, j.line_no;

第 2、3 张订单的明细路径没有产生行，因此交叉连接没有结果可以与父记录组合。订单是否确实应该出现在报表里，要由业务定义；若报表展示全部订单，这条连接就表达错了需求。

这里 JSON_TABLE 引用了前面的 o.detail，属于它支持的关联使用方式，不需要再机械添加 LATERAL 关键字。列别名 j 则不能省略，否则后面没有清晰的引用对象。

换成 LEFT JOIN，把无明细父记录留在结果里

SELECT o.id, j.line_no, j.sku
FROM sales_order AS o
LEFT JOIN JSON_TABLE(
    o.detail, '$.items[*]'
    COLUMNS (
        line_no FOR ORDINALITY,
        sku VARCHAR(30) PATH '$.sku' NULL ON EMPTY ERROR ON ERROR
    )
) AS j ON TRUE
WHERE o.tenant_id = 7
ORDER BY o.id, j.line_no;

现在空数组与缺失数组的父订单可以保留，j 的列在补出的行里为空。ON TRUE 表示不再增加匹配条件，并不是说所有文档都一定产生了一条真实明细。

这也解释了紧接着会出现的另一个错误：聚合用 COUNT(*) 后，无明细订单被数成一条。外连接保留的是一条补空结果，不是一条业务明细；要统计实际展开行，可以使用有序号的列。

SELECT o.id, COUNT(j.line_no) AS item_count
FROM sales_order AS o
LEFT JOIN JSON_TABLE(
    o.detail, '$.items[*]'
    COLUMNS (line_no FOR ORDINALITY)
) AS j ON TRUE
WHERE o.tenant_id = 7
GROUP BY o.id
ORDER BY o.id;

种子样本里 id=1 应有两条明细，id=2、3 为零，id=4、5 各有一条。数量字段是否合法，与是否存在一条数组元素是不同判断，先不要混在这个计数里。

把筛选放进 WHERE，可能又把外连接变回去了

假如只想展示每张订单中 SKU 为 A 的明细，可以把条件放在连接条件：

SELECT o.id, j.line_no, j.sku
FROM sales_order AS o
LEFT JOIN JSON_TABLE(
    o.detail, '$.items[*]'
    COLUMNS (
        line_no FOR ORDINALITY,
        sku VARCHAR(30) PATH '$.sku' NULL ON EMPTY ERROR ON ERROR
    )
) AS j ON j.sku = 'A'
WHERE o.tenant_id = 7
ORDER BY o.id, j.line_no;

若写成 WHERE j.sku = 'A'，补空行不能满足这个条件，没有 A 明细的订单就会再次消失。两种写法对应不同问题：一条问“保留订单并展示匹配明细”，另一条问“只保留存在匹配明细的展开结果”。没有统一的正确写法，只有是否符合报表口径。

如果页面还需要同时显示订单总明细数和 A 明细数，不要先在连接里删掉其他 SKU 再算总数。应保留完整展开范围，再用条件聚合表达各项指标，或者先在独立层次聚合后关联。

数量转换失败不能悄悄变成正常零值

先观察允许空结果的解析：

SELECT o.id, j.line_no, j.has_qty, j.qty
FROM sales_order AS o
LEFT JOIN JSON_TABLE(
    o.detail, '$.items[*]'
    COLUMNS (
        line_no FOR ORDINALITY,
        has_qty INT EXISTS PATH '$.qty',
        qty INT PATH '$.qty' NULL ON EMPTY NULL ON ERROR
    )
) AS j ON TRUE
WHERE o.tenant_id = 7
ORDER BY o.id, j.line_no;

EXISTS PATH 判断字段是否存在，不负责证明它能转换成合法数量。第 4 行的明细有 qty 键，但值是 JSON null；第 5 行的字符串 oops 则属于转换问题。允许 NULL ON ERROR 后，单看 qty 的 SQL NULL 可能分不清两者。

因此，报表不能一律把 NULL 改为零，再宣称数量统计完成。零数量、数量未知、格式错误和完全没有明细，都有不同含义。若这些状态不应该进入正式数据，最好在写入入口拒绝，而不是在每个统计 SQL 里补猜测规则。

用严格转换单独验证坏样本：

-- 预期失败：字符串oops不能作为本例要求的整数数量。
SELECT j.qty
FROM sales_order AS o
CROSS JOIN JSON_TABLE(
    o.detail, '$.items[*]'
    COLUMNS (qty INT PATH '$.qty' ERROR ON EMPTY ERROR ON ERROR)
) AS j
WHERE o.id = 5;

错误样本应保持可识别，方便修正原始输入。捕获异常后直接返回一个空结果，会把坏数据伪装成订单没有明细。

聚合之前先确定异常计数的含义

下面同时返回数组元素数、缺失或无法转换数量的元素数，以及可用数量之和。它展示的是诊断口径，不把缺失数量自动解释为合法零：

SELECT o.id,
       COUNT(j.line_no) AS element_count,
       SUM(CASE WHEN j.line_no IS NOT NULL AND j.qty IS NULL
                THEN 1 ELSE 0 END) AS unresolved_qty_count,
       SUM(j.qty) AS known_qty_sum
FROM sales_order AS o
LEFT JOIN JSON_TABLE(
    o.detail, '$.items[*]'
    COLUMNS (
        line_no FOR ORDINALITY,
        qty INT PATH '$.qty' NULL ON EMPTY NULL ON ERROR
    )
) AS j ON TRUE
WHERE o.tenant_id = 7
GROUP BY o.id
ORDER BY o.id;

这样，空数组可以显示零个元素，而异常数量不会从诊断中消失。是否把空集合的 known_qty_sum 展示为零，可以在展示层决定；只要 unresolved_qty_count 不为零，就不能把已知部分之和当成完整业务数量。

ON EMPTY 与 ON ERROR 的顺序也需要固定。MySQL 8.4 支持的语法应按空路径处理在前、错误处理在后书写，避免沿用旧例里含糊的顺序。JSON null 的处理还存在明确版本边界：MySQL 8.0.21 及以后，JSON_TABLE 对相应 JSON null 可返回 SQL NULL，不能把早期版本的报错行为泛化到 8.4。

两个数组展开时，先想清楚要不要相乘

如果同一订单还有优惠券数组，把明细和优惠券各自展开后交叉连接，可能产生“明细数乘优惠券数”的结果。此时再 SUM 明细数量，会把同一明细重复累加。外连接保住父记录，并不解决展开维度之间的基数问题。

若目标是各自统计，应分别聚合两个数组，再按订单关联；若目标确实是每个明细与每张优惠券的组合，乘积才符合需求。先写出期望的一张订单会产生多少行，比在结果翻倍后加 DISTINCT 更可靠。

FOR ORDINALITY 在这个过程中只是文档展开位置，不是稳定的业务明细标识。数组重排会改变序号，不能拿它替代已有 line_id 做长期关联。需要跨更新追踪单条明细，就应在数据契约里保留稳定标识。

LIMIT 十行，可能只截到了半张订单

展开后的每条明细都是结果集中的一行。若直接在最外层写 LIMIT 10，它限制的是展开行数，不是十张父订单；一张明细很多的订单可能占满整页，最后一张订单还可能只返回部分明细。前端随后按订单分组，会误以为数据库漏了某些行。

需要按订单翻页时，先确定父记录集合，再展开这些父记录的明细。例如本例先取前三张订单：

WITH picked AS (
    SELECT id, detail FROM sales_order
    WHERE tenant_id = 7
    ORDER BY id
    LIMIT 3
)
SELECT p.id, j.line_no, j.sku
FROM picked AS p
LEFT JOIN JSON_TABLE(
    p.detail, '$.items[*]'
    COLUMNS (
        line_no FOR ORDINALITY,
        sku VARCHAR(30) PATH '$.sku' NULL ON EMPTY ERROR ON ERROR
    )
) AS j ON TRUE
ORDER BY p.id, j.line_no;

这样父订单页大小与明细总行数可以不同，接口协议应明确返回结构和是否存在明细上限。若业务允许每张订单有无界明细，父级分页也不能替代单张大文档的容量约束。

父集合内的排序也应明确到唯一键。只按不唯一的创建时间取前三张订单，页边界可能随执行计划改变；展开层再按主键排序，不能修复父集合已经选错的那部分记录。

类型转换成功，也未必满足原始数据规则

NULL ON ERROR 主要处理提取或转换失败，不是完整的业务校验器。字符串形式的数字可能被成功转换；若业务要求 qty 必须原生为 JSON 数字，还需要检查原始 JSON 类型，不能只看转成 INT 之后是不是非空。

部分精度截断还可能产生警告，而不是按想象进入 ON ERROR。决定数量是否合法，应把字段类型、取值范围和允许的小数规则写在载荷约定里，不能借一个转换子句顺便完成所有验证。

当诊断发现异常数量时，可以根据 line_no 回到原数组元素检查原值。这个序号只用于当前文档的定位，数组已经更新后需要重新核对，不能拿旧序号去修改另一版文档的相同位置。若系统存在并发编辑，应同时验证文档版本，防止修复操作命中了已经移动的元素。

本例最后一条聚合查询已经给出可核验结果：父订单不丢，空数组不误计，坏数量没有被改写成零。确认这三个事实，再将同样口径接到报表接口。