MySQL JSON_ARRAYAGG 控制聚合数组的排序与长度

MySQL 8.4 中，普通写法 JSON_ARRAYAGG(expr) 不能保证数组元素顺序，也没有内置的 LIMIT。要得到“每个订单最新 3 条明细，并按最新到最旧排列”的 JSON 数组，可靠方案是两步：先用 ROW_NUMBER() 按分组编号并筛出前 N 条，再把 JSON_ARRAYAGG() 作为窗口函数，通过窗口 ORDER BY 固定顺序，并显式写出完整窗口帧。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/aggregate-functions.html

背景：JSON_ARRAYAGG 只负责聚合，不承诺顺序

JSON_ARRAYAGG(col_or_expr) 会把多行值组成一个 JSON 数组。MySQL 8.4 官方文档明确写明，普通聚合形式中的元素顺序未定义。这意味着同一条 SQL 今天看起来按主键递增，换索引、统计信息、并行策略或执行计划后都可能变化。

下面这条 SQL 能按订单生成数组，但不能把输出顺序视为契约：

SELECT
    order_id,
    JSON_ARRAYAGG(
        JSON_OBJECT('id', id, 'sku', sku, 'quantity', quantity)
    ) AS items_json
FROM order_items
-- 普通聚合只按订单分组，不保证数组内部元素顺序。
GROUP BY order_id;

另一个常见误解是给最外层查询加 ORDER BY created_at DESC LIMIT 3。它限制的是最终结果集的行数，不是每个 order_id 对应数组中的元素数量。

旧写法问题：子查询排序不是数组顺序契约

有些写法先在派生表中排序，再在外层执行 JSON_ARRAYAGG()。这种写法可能暂时得到期望顺序，但外层聚合仍没有显式的顺序语义，优化器也不需要为聚合保留派生表的物理行序。只要数组顺序要提供给 API 或缓存，就应该把排序放入真正控制窗口计算的 OVER(... ORDER BY ...) 中。

新规则：窗口 ORDER BY 决定数组元素顺序

MySQL 8.4 允许 JSON_ARRAYAGG() 带 OVER 子句，从而作为窗口函数执行。窗口中的 PARTITION BY 划分每组数据，ORDER BY 决定分区内处理顺序，frame 决定当前行能看到分区中的哪些行。

如果只写 ORDER BY 而省略 frame，常见结果是每一行得到一个逐步扩大的“运行数组”。为了让分区内每一行都看到完整数组，应显式使用：

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
-- 这个窗口帧覆盖整个分区，避免只聚合到当前行。

排序键还要具有确定性。若多个明细的 created_at 相同，应追加唯一的 id 作为并列值的决胜键，否则这些并列行之间仍没有固定顺序。

代码对比：每组最新 3 条并稳定排序

下面以 order_items(order_id, id, sku, quantity, created_at) 为例。第一层 CTE 给每个订单的明细编号，第二层只保留前 3 条，再在完整窗口帧内构造数组。最后用另一个行号从每组重复的窗口结果中取一行。

WITH ranked AS (
    SELECT
        order_id,
        id,
        sku,
        quantity,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY created_at DESC, id DESC
        ) AS rn
    FROM order_items
),
aggregated AS (
    SELECT
        order_id,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'id', id,
                'sku', sku,
                'quantity', quantity,
                'created_at', created_at
            )
        ) OVER (
            PARTITION BY order_id
            ORDER BY rn
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS items_json,
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY rn
        ) AS output_row
    FROM ranked
    -- rn 在每个订单内从 1 开始，因此这里限制的是每组数组长度。
    WHERE rn 

这里的 rn=1 代表最新一条，窗口按 rn 升序聚合，所以数组顺序是最新到最旧。若要改成最旧到最新，可调整第一层编号方向或第二层窗口的排序方向，但要保证长度筛选仍选中真正需要的 N 条记录。

长度控制：先减少参与聚合的行

JSON_ARRAYAGG() 没有“每组只取 N 个元素”的参数。控制元素数量最清晰的方式，是在聚合前减少参与窗口计算的行。ROW_NUMBER() 适用于每组 Top N；固定时间范围可以直接用 WHERE created_at >= ...；只聚合有效状态则在编号之前先过滤状态，避免无效行占据前 N 名。

如果业务允许每组数量不同，可以把上限放在配置表中，与明细行连接后使用 rn 。但上限必须保持合理；即使元素个数不多，单个 JSON 对象里的长文本也可能让结果很大。

元素个数与字节大小是两回事

JSON_LENGTH(items_json) 可检查顶层数组元素个数，适合验证“最多 3 条”这类业务规则。JSON_STORAGE_SIZE(items_json) 返回 MySQL 二进制 JSON 表示使用的字节数，适合观察存储或传输压力，但它不是聚合函数的限制参数。

WITH result AS (
    -- 这里放入上一节最终查询，保持每个订单只返回一行。
    SELECT order_id, items_json
    FROM prepared_order_json
)
SELECT
    order_id,
    JSON_LENGTH(items_json) AS item_count,
    JSON_STORAGE_SIZE(items_json) AS json_bytes
FROM result
-- 同时观察元素数量和二进制 JSON 字节数。
ORDER BY json_bytes DESC;

上面的 prepared_order_json 代表已经封装好的视图或中间结果，示例重点是区分两个指标。max_allowed_packet 是服务器与客户端单条消息大小相关的上限，不应被当作“每组数组最多几个元素”的业务控制方式。

兼容注意：不要照搬 GROUP_CONCAT 语法

GROUP_CONCAT() 支持在函数内部写 ORDER BY 和 SEPARATOR，但 MySQL 8.4 的 JSON_ARRAYAGG() 语法是 JSON_ARRAYAGG(expr) [over_clause]。因此下面这种看似自然的写法并不是 MySQL 8.4 的有效语法：

-- 错误示意：MySQL 8.4 不支持把 ORDER BY 和 LIMIT 直接写进 JSON_ARRAYAGG 参数。
SELECT JSON_ARRAYAGG(value ORDER BY created_at DESC LIMIT 3)
FROM events;

也不要用 GROUP_CONCAT(JSON_OBJECT(...)) 手工拼方括号来替代 JSON 聚合，除非你愿意承担转义、NULL、截断和类型语义的额外风险。需要兼容不支持窗口版 JSON 聚合的旧版本时，更稳妥的选择往往是先用 SQL 按组取出有序数据，再由应用层编码 JSON。

采用建议：索引要服务于分组与排序

这类查询的主要成本来自“按组排序并取前 N 条”。以示例为准，可以评估 (order_id, created_at DESC, id DESC) 复合索引，让分组键和排序键尽量对齐。是否真正使用索引仍要结合数据分布、过滤条件和 EXPLAIN 判断，不能只凭索引名称下结论。

当每组明细非常多、请求频率高时，可考虑把最新 N 条拆成专用查询，由应用层聚合，或在写入链路维护面向读取的摘要表。窗口函数写法语义清晰，但不代表所有数据规模下都最省成本。

常见问题

外层 ORDER BY 能改变 JSON 数组内部顺序吗？

不能。外层 ORDER BY 排的是查询结果行；数组内部顺序要写进 JSON_ARRAYAGG() OVER(... ORDER BY ...) 的窗口定义。

为什么我只取 output_row=1 时数组只有一个元素？

通常是遗漏了完整窗口帧。有序窗口若只看到当前行之前的 frame，第一行得到的就是单元素运行数组。显式写 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

LIMIT 3 为什么不能限制每个订单的数组长度？

普通 LIMIT 作用于最终结果集。每组 Top 3 需要先用 ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) 编号，再用 WHERE rn 过滤。

created_at 相同会不会再次乱序？

会有不确定性。为排序追加唯一且稳定的决胜键，例如 ORDER BY created_at DESC, id DESC，让同一分区内每一行都有确定位置。