CODEWITHSUNDEEP
sqljsondatabasepostgresqljoin
Nicola Gaioni
Nicola Gaioni

Reputation: 178

PostgreSQL - query to select data from many-to-many tables

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"}}
]

Upvotes: 1

Views: 1367

Answers (3)

Stefanov.sm
Stefanov.sm

Reputation: 13049

"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;

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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

Upvotes: 1

Nick
Nick

Reputation: 147146

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

Upvotes: 1

Related Questions