PostgreSQL使用ARRAY_AGG返回合并数组，如何得到单游戏对应国家ID数组

你编写的CTE中仅按user_id分组，会将对应用户名下所有游戏的国家ID全部聚合到同一个数组中，因此user_id=1的两个游戏返回的都是该用户所有游戏关联的国家ID合集（Monopoly关联的1、2、3加Zelda关联的2，合并为{1,2,3,2}），不符合按单个游戏聚合的需求。

将聚合的分组键调整为游戏ID，每个游戏单独聚合自身关联的国家ID即可，有两种常用写法：

写法1：简化版（无需CTE） #

SELECT 
    g.name,
    ARRAY_AGG(gcx.country_id) AS country_ids
FROM games g
LEFT JOIN games_countries_xref gcx ON g.id = gcx.game_id
WHERE g.user_id = 1
GROUP BY g.id, g.name;

写法2：保留CTE结构的修改版 #

WITH game_country_ids AS (
    SELECT 
        g.id AS game_id, 
        g.user_id, 
        g.name, 
        ARRAY_AGG(gcx.country_id) AS country_ids
    FROM games AS g
    LEFT JOIN games_countries_xref AS gcx ON g.id = gcx.game_id
    GROUP BY g.id, g.user_id, g.name
)
SELECT name, country_ids
FROM game_country_ids
WHERE user_id = 1;

两种写法执行后都可以得到你期望的输出结果。

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