如何在PostgreSQL主表查询中获取从表的多字段结果数组？

问题说明 #

现有PostgreSQL多对多关联表结构：

• shifts表：存班次信息，含date（日期）、type（仅"Morning Shift"/"Evening Shift"）、id（主键）
• users表：存用户信息，含id（主键）、username、role、tag
• shifts_users表：关联用户与班次的中间表，关联shifts.id和users.id

需求是按日期分组，每个日期下区分早/晚班，每个班次返回该班次所有用户的完整信息（用户名、角色、标签），预期输出格式：

{
  "2023-01-01": {
    "morning_shift": [
      { "username": "Some user", "role": "Some role", "tag": "Some tag" },
      ...
    ],
    "evening_shift": [
      { "username": "Some user", "role": "Some role", "tag": "Some tag" },
      ...
    ]   
  },
  ...
}

原查询仅能获取用户名，无法返回完整用户字段，且存在逻辑错误（子查询未关联外层日期，会把所有早班用户放到每个日期下）。

解决方案 #

方法1：直接生成符合需求的JSON结构 #

利用PostgreSQL的JSON函数，构造完整用户对象并按日期聚合：

SELECT json_object_agg(
  s.date::text,
  json_build_object(
    'morning_shift', COALESCE(
      json_agg(
        json_build_object(
          'username', u.username,
          'role', u.role,
          'tag', u.tag
        )
      ) FILTER (WHERE s.type = 'Morning Shift'),
      '[]'::json
    ),
    'evening_shift', COALESCE(
      json_agg(
        json_build_object(
          'username', u.username,
          'role', u.role,
          'tag', u.tag
        )
      ) FILTER (WHERE s.type = 'Evening Shift'),
      '[]'::json
    )
  )
) AS shift_user_list
FROM shifts s
LEFT JOIN shifts_users su ON su.shift_id = s.id
LEFT JOIN users u ON u.id = su.user_id
GROUP BY s.date
ORDER BY s.date ASC;

• json_build_object用于构造单个用户的JSON对象
• FILTER子句筛选对应班次的用户
• COALESCE确保无用户的班次返回空数组而非null
• json_object_agg直接生成以日期为键的顶层JSON结构

如果先按日期返回拆分后的字段，再自行处理结构，可用简化版：

SELECT 
  s.date,
  COALESCE(
    json_agg(json_build_object('username', u.username, 'role', u.role, 'tag', u.tag)) 
    FILTER (WHERE s.type = 'Morning Shift'), '[]'::json
  ) AS morning_shift,
  COALESCE(
    json_agg(json_build_object('username', u.username, 'role', u.role, 'tag', u.tag)) 
    FILTER (WHERE s.type = 'Evening Shift'), '[]'::json
  ) AS evening_shift
FROM shifts s
LEFT JOIN shifts_users su ON su.shift_id = s.id
LEFT JOIN users u ON u.id = su.user_id
GROUP BY s.date
ORDER BY s.date ASC;

方法2：返回PostgreSQL原生行类型数组 #

如果不需要JSON格式，可直接返回用户行的数组（后续可自行转换为JSON）：

SELECT 
  s.date,
  COALESCE(array_agg(u) FILTER (WHERE s.type = 'Morning Shift'), '{}'::users[]) AS morning_shift,
  COALESCE(array_agg(u) FILTER (WHERE s.type = 'Evening Shift'), '{}'::users[]) AS evening_shift
FROM shifts s
LEFT JOIN shifts_users su ON su.shift_id = s.id
LEFT JOIN users u ON u.id = su.user_id
GROUP BY s.date
ORDER BY s.date ASC;

表结构优化建议 #

当前多对多结构是合理的，无需调整。可通过添加索引提升查询效率：

• 给shifts表的date+type加联合索引，加速分组过滤
• 给shifts_users表的shift_id+user_id加联合索引，加速关联查询

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