Using array_agg with multiple DISTINCT Columns

Question

In this query, I'm listing all users in organization 123 but I also want a column showing which other teams they are on across all organizations.

My query right now will give me the team names but I'd also like to get the team id as well. The DISTINCT is necessary because they user may have different roles on the same team.

Bonus points if I can sort the teams by when the user was given a role, which currently gives an error as I have it now.

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

This returns:

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

What I'd like to return is the team name with its primary key id:

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

EDIT

Or better yet:

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

Considering that your query is working fine, replace the following section of your query mentioned in question:

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

with

(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

Here is how I ended up solving it, building off of @akhilesh answer.

  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