如何限制关联查询返回的分类对应游戏数组的元素数量？

有如下SQL查询，该查询返回分类信息以及与之关联的游戏数组。请问如何限制每个分类对应游戏数组中的游戏数量？

原SQL代码：

SELECT categories.*, COALESCE(jsonb_agg(g.*) FILTER (WHERE g.name IS NOT NULL), '[]') AS games FROM categories
    LEFT JOIN games_categories gc ON categories.id = gc.category_id
    LEFT JOIN games g ON gc.game_id = g.id
    GROUP BY categories.id;

要实现每个分类只返回指定数量的游戏，核心思路是先给每个分类下的游戏按规则排序编号，过滤掉超出数量限制的条目后再聚合为JSON数组。以下以每个分类最多返回3个游戏为例：

SELECT 
    categories.*,
    COALESCE(jsonb_agg(filtered_games.*) FILTER (WHERE filtered_games.name IS NOT NULL), '[]') AS games
FROM categories
LEFT JOIN (
    -- 子查询为每个分类下的游戏排序并编号
    SELECT 
        gc.category_id,
        g.*,
        ROW_NUMBER() OVER (PARTITION BY gc.category_id ORDER BY g.id) AS rn
        -- 可替换排序规则，比如按发布日期倒序：ORDER BY g.release_date DESC
    FROM games_categories gc
    JOIN games g ON gc.game_id = g.id
) AS filtered_games ON categories.id = filtered_games.category_id
WHERE filtered_games.rn <= 3 OR filtered_games.rn IS NULL
GROUP BY categories.id;

关键细节说明： #

• ROW_NUMBER() OVER (PARTITION BY gc.category_id ORDER BY ...)：通过PARTITION BY按分类分组，ORDER BY指定游戏的排序逻辑（可根据业务需求替换为游戏名称、热度等字段），给每个分类下的游戏生成唯一编号。
• WHERE filtered_games.rn <= 3 OR filtered_games.rn IS NULL：既保留每个分类内前3个游戏，也确保没有关联游戏的分类能正常返回空数组[]。
• 若要调整游戏数量，只需修改<=后的数字即可。

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