I have a problem with a query to select some data from my database,

My database has 3 tables: movies, actors, movies_actors.

ACTORS TABLE
 ----------- 
| ID | NAME |
 ----------- 
|  1 | Bob  |
 ----------- 
|  2 | John |
 ----------- 

MOVIES TABLE
 ----------- 
| ID | TITLE|
 ----------- 
|  1 | aaa  |
 ----------- 
|  2 | bbb  |
 ----------- 

MOVIES-ACTORS TABLE (MANY-TO-MANY)
 -------------------- 
| MOVIE_ID| ACTOR_ID |
 -------------------- 
|  1      | 1        |
 -------------------- 
|  1      | 2        |
 -------------------- 
|  2      | 1        |
 -------------------- 

I need to get all the movies, and inside every movie i need to have a property called actors, that should be an array containing all the actors that are related to that movie.

the response should look something like this:

[
 {id: 1, title: "aaa", actors: [{id: 1, name: "Bob"}, {id: 2, name: "John"}]},
 {id: 2, title: "bbb", actors: [{id: 1, name: "Bob"}]}
]

What is the best way to achieve this result? I'm able to do this with 2 different requests and mapping and filtering the results, but it doesn't seem right to me. So i tried to write a single query using JOIN but i was only able to get a "movie" row for every actor in it.

I will share it because i think it's not far from the solution.

SELECT movies.*, json_build_object('name', actor.name, 'id', actor.id) AS actors
FROM movies
LEFT OUTER JOIN movies_actors
ON movies.id = movies_actors.movie_id
LEFT OUTER JOIN actors
ON movies_actors.actor_id = actors.id

and this is what I'm getting back so far:

[
 {id: 1, title: "aaa", actors: {id: 1, name: "Bob"}},
 {id: 1, title: "aaa", actors: {id: 2, name: "John"}},
 {id: 2, title: "bbb", actors: {id: 1, name: "Bob"}}
]

CodePudding user response：

You need to GROUP BY the movies.id (I assume it's a primary key) and use json_agg to build your actors object:

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

Output (for your sample data):

id  title   actors
1   aaa     [{"name":"Bob","id":1},{"name":"John","id":2}]
2   bbb     [{"name":"Bob","id":1}]

Demo on db-fiddle

CodePudding user response：

So far so good, what you need to add as extra is JSON_AGG() function along with a GROUP BY expression such as

SELECT JSON_AGG(result)
  FROM
  (
   SELECT JSON_BUILD_OBJECT(
                            'id', m.id, 
                            'title', m.title,
                            'actors',JSON_AGG(JSON_BUILD_OBJECT('id', a.id, 'name', a.name))
                          ) AS result
     FROM movies AS m
     LEFT JOIN movies_actors AS ma
       ON m.id = ma.movie_id
     LEFT JOIN actors AS a
       ON ma.actor_id = a.id
    GROUP BY m.id, m.title
  ) AS j

Demo

CodePudding user response：

"Knit" the JSON structure trivially step by step, inside out first aggregate actors' id/name pairs list (ti) then aggregate movies with their actors lists (tx).

select json_agg(tx) from
(
 select id, title, 
 (
  select json_agg(ti) from 
  (
   select id, "name" 
   from actors join movies_actors on actors.id = actor_id 
   where movie_id = movies.id
  ) ti 
 ) as actors 
 from movies
) tx;