PostgreSQL多对多表查询：如何将关联演员聚合为JSON数组

直接使用分组+JSON聚合函数即可实现需求，以下是最终查询语句：

SELECT 
    movies.id,
    movies.title,
    json_agg(json_build_object('id', actors.id, 'name', actors.name)) AS actors
FROM movies
LEFT JOIN movies_actors ON movies.id = movies_actors.movie_id
LEFT JOIN actors ON movies_actors.actor_id = actors.id
GROUP BY movies.id, movies.title;

关键逻辑说明： #

• GROUP BY movies.id, movies.title：将同一部电影的多条关联记录合并为一行，避免重复的电影条目
• json_agg()：PostgreSQL内置的JSON聚合函数，会把分组内的所有演员JSON对象打包成一个数组
• 保留LEFT JOIN：确保即使没有关联演员的电影也会被查询出来（此时actors字段会返回包含null的数组，若要优化为空数组可看下面的进阶写法）

进阶优化（处理无演员的电影返回空数组） #

如果希望没有演员的电影的actors字段返回[]而非包含null的数组，可以添加COALESCE和过滤条件：

SELECT 
    movies.id,
    movies.title,
    COALESCE(
        json_agg(json_build_object('id', actors.id, 'name', actors.name)) FILTER (WHERE actors.id IS NOT NULL),
        '[]'::json
    ) AS actors
FROM movies
LEFT JOIN movies_actors ON movies.id = movies_actors.movie_id
LEFT JOIN actors ON movies_actors.actor_id = actors.id
GROUP BY movies.id, movies.title;

你之前的查询未做分组和聚合，所以每个演员对应一条电影记录，通过上述分组+聚合的方式就能高效实现单条查询返回期望结构的需求。

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