PostgreSQL多表关联时string_agg函数不生效问题排查

问题根源 #

你当前的查询里JOIN了user_roles表，这会让每个用户的每条角色记录都生成一行数据——哪怕子查询里已经把角色聚合为逗号分隔的字符串，主查询还是会因为user_roles的多条记录输出重复行。

解决方案1：移除主查询中的user_roles关联 #

因为子查询已经通过user_roles关联获取角色，主查询不需要再JOIN该表，这样每个用户只会返回一行数据：

SELECT 
  users.user_name, 
  user_profiles.first_name, 
  user_profiles.last_name,
  (
    SELECT string_agg(roles.name, ',') 
    FROM roles 
    JOIN user_roles ON roles.id = user_roles.role_id
    WHERE user_roles.user_id = users.id
  ) AS roles
FROM users 
JOIN user_profiles ON users.id = user_profiles.user_id;

解决方案2：用GROUP BY分组聚合 #

如果需要保留JOIN写法，可通过对用户的唯一标识字段分组，直接在主查询中聚合角色名，合并重复行：

SELECT 
  users.user_name, 
  user_profiles.first_name, 
  user_profiles.last_name,
  string_agg(roles.name, ',') AS roles
FROM users 
JOIN user_profiles ON users.id = user_profiles.user_id
JOIN user_roles ON user_roles.user_id = users.id
JOIN roles ON roles.id = user_roles.role_id
GROUP BY users.id, user_profiles.first_name, user_profiles.last_name, users.user_name;

方案说明 #

• 方案1适合不需要在主查询中使用user_roles其他字段的场景，写法简洁；
• 方案2适合需要关联角色表更多字段，或习惯用JOIN串联表结构的场景。

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