PostgreSQL一对多表关联查询：将公司评论聚合为数组返回

针对你的需求，我们可以用PostgreSQL的array_agg()函数把每个公司对应的所有评论整合成数组，同时保留公司的全部基础信息。以下是完整的SQL语句：

SELECT
  companies.*,
  COALESCE(
    array_agg(
      json_build_object(
        'review_id', reviews.id,
        'title', reviews.title,
        'review_text', reviews.review,
        'trust_score', reviews.trust_score,
        'created_at', reviews.created_at,
        'user', reviews.user
      )
    ),
    '{}'::json[]
  ) AS company_reviews
FROM companies
LEFT JOIN reviews ON companies.company_id = reviews.company
GROUP BY companies.company_id;

代码解释： #

• LEFT JOIN：保证即使没有评论的公司也会被包含在结果里，不会被过滤掉
• array_agg()：把关联到当前公司的所有评论行，打包成一个数组
• json_build_object()：把每条评论的多个字段（比如标题、内容、评分等）整合成一个JSON对象，让数组里的每个元素都是结构清晰的评论信息，方便后续使用
• COALESCE(..., '{}'::json[])：如果某个公司没有评论，array_agg()会返回NULL，这个函数会把NULL替换成空的JSON数组，避免结果出现空值
• GROUP BY companies.company_id：按照公司ID分组，确保每个公司只出现在结果的一行里，对应的评论数组包含该公司的所有评论

简化版（按需选择）： #

如果你只需要评论的部分字段，比如只保留标题和内容，可以简化json_build_object()的内容：

json_build_object('title', reviews.title, 'review_text', reviews.review)

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