How to aggregate an array of JSON objects with Postgres?

Viewed 19165

I'm looking to aggregate an array of JSON objects with Postgres, specifically for returning a list of relationships to another table by foreign key. In this case it's a user and their teams.

Here's the schema I'm working with:

CREATE TABLE teams (
  id TEXT PRIMARY KEY,
  ...
);

CREATE TABLE users (
  id TEXT PRIMARY KEY,
  ...
);

CREATE TABLE memberships (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL FOREIGN KEY (user_id) REFERENCES users(id),
  team_id TEXT NOT NULL FOREIGN KEY (team_id) REFERENCES teams(id)
);

With the following query:

  SELECT
    users.id,
    ...
    CASE
      WHEN count(teams.*) = 0
      THEN '[]'::JSON
      ELSE json_agg(DISTINCT teams.id)
    END AS teams
  FROM users
  LEFT JOIN memberships ON users.id = memberships.user_id
  LEFT JOIN teams ON teams.id = memberships.team_id
  WHERE users.id = $[userId]
  GROUP BY
    users.id,
    ...

I can get results as a flat array of team_ids:

{
  id: 'user_1',
  ...
  teams: ['team_1', 'team_2']
}

But I'd like to receive the results as JSON objects instead:

{
  id: 'user_1',
  ...
  teams: [
    { id: 'team_1' },
    { id: 'team_2' }
  ]
}

I get extremely close with:

  SELECT
    users.id,
    ...
    CASE
      WHEN count(teams.*) = 0
      THEN '[]'::JSON
      ELSE json_agg(json_build_object('id', teams.id))
    END AS teams
  FROM users
  LEFT JOIN memberships ON users.id = memberships.user_id
  LEFT JOIN teams ON teams.id = memberships.team_id
  WHERE users.id = $[userId]
  GROUP BY
    users.id,
    ...

But now I've lost the DISTINCT function's de-duping of results, so I end up with duplicate IDs returned for each team.

2 Answers
Related