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

Nicola Gaioni 0
Viewed 42

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.

join sql postgresql json
1 Answers

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
;
userTMKw0
Related