Query to select data from 3 or more many-to-many tables-CodePudding

Using PostgreSQL, I'm trying to select all data from the table "movies" with all related data from other 2 many-to-many tables.

My database has 5 tables: movies, actors, studios, movies_actors, movies_studios.

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

STUDIOS TABLE
 ---------------- 
| ID | NAME      |
 ---------------- 
|  1 | studio A  |
 ---------------- 
|  2 | studio B  |
 ---------------- 

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

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

MOVIES-STUDIOS TABLE (MANY-TO-MANY)
 -------------------- 
| MOVIE_ID| STUDIO_ID|
 -------------------- 
|  1      | 1        |
 -------------------- 
|  1      | 2        |
 -------------------- 
|  2      | 1        |
 --------------------

The response I'm looking for is this:

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

I will share here the query that I have until now, which works fine to join "actors" and "movies", but I don't know how to make it work with the third table "studios".

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

Basically, I need to do the same that I'm doing with "movies_actors" and "actors", but then I can't make "GROUP BY movies.id" work as expected. This is the issue.

CodePudding user response：

This should do the trick and is a common technique (see another example here):

SELECT
    movies.*,
    movies_actors_JSON.actors_JSON,
    movies_studios_JSON.studio_JSON
FROM
    movies
    LEFT JOIN 
        ( -- one row per movie_id
        SELECT
            movies_actors.movie_id,
            JSON_AGG(
                JSON_BUILD_OBJECT(
                    'id', actors.id,
                    'name', actors.name                 
                    )
                ) AS actors_JSON
        FROM
            movies_actors
            JOIN actors ON actors.id = movies_actors.actor_id
        GROUP BY
            movies_actors.movie_id
        ) movies_actors_JSON
        ON movies.id = movies_actors_JSON.movie_id
    LEFT JOIN 
        ( -- one row per movie_id
        SELECT
            movies_studios.movie_id,
            JSON_AGG(
                JSON_BUILD_OBJECT(
                    'id', studios.id,
                    'name', studios.name
                    )
                ) AS studios_JSON
        FROM
            movies_studios
            JOIN studios ON studios.id = movies_studios.studio_id
        GROUP BY
            movies_studios.movie_id
        ) movies_studios_JSON
        ON movies.id = movies_studios_JSON.movie_id
;