sql - 如何使用 Postgres 聚合 JSON 对象数组？

Question

我希望使用 Postgres 聚合一组 JSON 对象，专门用于通过外键将关系列表返回到另一个表。在这种情况下，它是一个用户和他们的团队。

这是我正在使用的架构:

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)
);

使用以下查询:

  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,
    ...

我可以将结果作为 team_id 的平面数组:

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

但我想接收结果作为 JSON 对象:

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

我非常接近:

  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,
    ...

但现在我丢失了 DISTINCT 函数的结果重复数据删除功能，因此我最终为每个 team 返回了重复的 ID。

Answer 1

您可以使用选择适当组合的子查询解决此问题，然后聚合到 json 数组中:

SELECT id, json_strip_nulls(json_agg(json_build_object('id', team))) AS teams
FROM (
  SELECT DISTINCT user_id AS id, team_id AS team
  FROM memberships
  WHERE user_id = $[userId]) sub
GROUP BY id;

您可以从 memberships 表中获取用户 ID 和团队 ID，因此将任何一个表连接到 memberships 表都没有意义(除非您获取其他字段来自那些你没有给我们看的表格)。如果您确实想使用其他字段，您可以将 JOIN 直接粘贴回去。

json_strip_nulls() 函数将摆脱 [{"id": null}] 的出现并将它们替换为空的 []::json.这是 PG 9.5 的新功能。这也摆脱了相当丑陋和低效的 CASE 子句。