如何在json_arrayagg()中添加LIMIT或ORDER BY？附关联表查询需求

核心结论 #

• json_arrayagg()里可以直接加ORDER BY：不管是MySQL还是PostgreSQL这类支持JSON聚合的数据库，都允许在聚合函数内部指定排序规则，用来控制生成的JSON数组元素顺序。
• json_arrayagg()里不能直接加LIMIT：聚合函数是把分组内所有符合条件的行打包成数组，LIMIT没法在聚合内部生效，得先对二级表的记录做筛选再聚合。

具体实现方案 #

场景1：全局限制二级表的记录数量（所有一级分组共用同一批二级数据） #

先对level_two_table做排序和LIMIT，再关联一级、三级表后聚合生成嵌套JSON，以MySQL为例：

SELECT
    JSON_OBJECT(
        'level_one_id', t1.id,
        'level_one_name', t1.name,
        'level_two_list', JSON_ARRAYAGG(
            JSON_OBJECT(
                'level_two_id', t2.id,
                'level_two_name', t2.name,
                'level_three', JSON_OBJECT(
                    'level_three_id', t3.id,
                    'level_three_name', t3.name
                )
            )
        )
    ) AS result
FROM level_one_table t1
LEFT JOIN (
    -- 这里按业务需求排序，比如按创建时间倒序取前5条二级记录
    SELECT * FROM level_two_table
    ORDER BY create_time DESC
    LIMIT 5
) t2 ON t1.id = t2.level_one_id
LEFT JOIN level_three_table t3 ON t2.id = t3.level_two_id
GROUP BY t1.id, t1.name;

场景2：每个一级分组下单独限制二级记录数量 #

这种情况需要用窗口函数（比如ROW_NUMBER()）给每个一级分组下的二级记录编号，再筛选出指定数量的记录，最后聚合：

SELECT
    JSON_OBJECT(
        'level_one_id', t1.id,
        'level_one_name', t1.name,
        'level_two_list', JSON_ARRAYAGG(
            JSON_OBJECT(
                'level_two_id', t2_filtered.id,
                'level_two_name', t2_filtered.name,
                'level_three', JSON_OBJECT(
                    'level_three_id', t3.id,
                    'level_three_name', t3.name
                )
            ) ORDER BY t2_filtered.create_time DESC -- 也可以在聚合内再次指定排序
        )
    ) AS result
FROM level_one_table t1
LEFT JOIN (
    SELECT *,
           -- 按一级id分组，给二级记录按创建时间倒序编号
           ROW_NUMBER() OVER (PARTITION BY level_one_id ORDER BY create_time DESC) AS rn
    FROM level_two_table
) t2_filtered ON t1.id = t2_filtered.level_one_id AND t2_filtered.rn <= 3 -- 每个一级分组取前3条
LEFT JOIN level_three_table t3 ON t2_filtered.id = t3.level_two_id
GROUP BY t1.id, t1.name;

如果你用PostgreSQL #

PG可以用子查询嵌套的方式更简洁地实现分组限制：

SELECT
    json_build_object(
        'level_one_id', t1.id,
        'level_one_name', t1.name,
        'level_two_list', (
            SELECT json_agg(
                json_build_object(
                    'level_two_id', t2.id,
                    'level_two_name', t2.name,
                    'level_three', json_build_object(
                        'level_three_id', t3.id,
                        'level_three_name', t3.name
                    )
                ) ORDER BY t2.create_time DESC
            )
            FROM level_two_table t2
            LEFT JOIN level_three_table t3 ON t2.id = t3.level_two_id
            WHERE t2.level_one_id = t1.id
            LIMIT 3 -- 每个一级分组取前3条二级记录
        )
    ) AS result
FROM level_one_table t1;

总结 #

排序需求直接在json_arrayagg()里加ORDER BY就能解决；限制数量必须先通过子查询或窗口函数预处理二级表数据，再进行聚合，根据你的业务场景选对应的方案就行。

内容的提问来源于stack exchange，提问作者user1775888