使用Array_agg与多个不同的列

Question

在此查询中，我正在列出组织123中的所有用户，但我还希望一列显示他们在所有组织中都参加的其他团队。

我的查询现在会给我带来团队名称，但我也想获得团队ID。独特之处是必要的，因为他们的用户可能在同一团队中具有不同的角色。

如果我可以在赋予用户扮演角色时通过对团队进行分类，那么奖励积分，目前会出现错误。

SELECT 
users.*,
(
    SELECT
        to_json(array_agg(DISTINCT teams.name ORDER BY teams.name))
    FROM roles r
    INNER JOIN user_roles ur ON ur.role_id=r.id AND ur.user_id=users.id
    INNER JOIN teams ON r.team_id=teams.id
    -- ORDER BY r.created_at
) teams

FROM users
INNER JOIN user_roles ON users_roles.user_id=users.id
INNER JOIN roles ON roles.id = user_roles.role_id
WHERE roles.type = 'admin' AND roles.organization_id = 123
GROUP BY users.id

返回：

name       | teams
John Smith | ['Team 1', 'Team 2']
Jane Doe   | ['Team 2', 'Team 3']

我想返回的是带有其主要密钥ID的团队名称：

name       | teams
John Smith | {1: 'Team 1', 2: 'Team 2'}
Jane Doe   | {2: 'Team 2', 3: 'Team 3'}

eding

或更高：

name       | teams
John Smith | [{id: 1, name: 'Team 1'}, {id: 2, 'Team 2'}]
Jane Doe   | [{id: 2, name: 'Team 2'}, {id: 3, 'Team 3'}]

Answer 1

考虑到您的查询工作正常，请替换所提到的查询的以下部分

SELECT
        to_json(array_agg(DISTINCT teams.name ORDER BY teams.name))
    FROM roles r
    INNER JOIN user_roles ur ON ur.role_id=r.id AND ur.user_id=users.id
    INNER JOIN teams ON r.team_id=teams.id
    -- ORDER BY r.created_at

：

(SELECT 
json_object(array_agg(id::text order by created_at desc),
array_agg(name order by created_at desc)) from 
   ( SELECT
        DISTINCT on (teams.id) teams.id, teams.name , r.created_at
    FROM roles r
    INNER JOIN user_roles ur ON ur.role_id=r.id AND ur.user_id=users.id
    INNER JOIN teams ON r.team_id=teams.id
    ORDER BY r.created_at
)tab)

Answer 2

这是我最终解决它的方法，并在@akhilesh的答案上建立。

  SELECT 
    json_object(
      array_agg(id :: text ORDER BY created_at DESC), 
      array_agg(name ORDER BY created_at DESC)
    ) 
  FROM 
    (
      SELECT 
        * 
      FROM 
        (
          SELECT 
            teams.id, 
            teams.name, 
            MAX(ur.created_at) created_at 
          FROM 
            roles r 
            INNER JOIN user_roles ur ON ur.role_id = r.id AND ur.user_id = users.id 
            INNER JOIN teams ON r.team_id=teams.id
          GROUP BY 
            teams.id
        ) T 
      ORDER BY 
        T.created_at DESC 
    ) teams