Aggregation based many collection

Question

I have 3 tables like

Actor

actor_id: "1"
first_name: "Penelope"
last_name: "Guiness"

Films

film_id: "1"
title: "ABC"

Film_Actor

film_id: "1"
actor_id: "22"

I want to get movies (title) starring by an actor, like

actor_id: "1"
title: {"ABC", "DEF", "GHI"}

in this case I have not been able to get it, what I just achieved is only aggregation based on 2 collections like:

db.actor.aggregate ([
{
    $ lookup:
{
from: 'film_actor',
localField: 'actor_id',
foreignField: 'actor_id',
as: 'film_id'
}   }
])

Answer 1

First of all I think you have a data model issue there. It looks like a SQL database and that's not the idea of using mongo.

But if you want to do it anyway you should do:

db.actor.aggregate([
  {
    $lookup: {
      from: "film_actor", // the collection you are trying to match
      localField: "actor_id",
      foreignField: "actor_id",
      as: "film_actor"
    }
  },
  {
    $lookup: {
      from: "films", // the collection you are trying to match
      localField: "film_actor.film_id", // or '$film_actor.actor_id' if it does not work
      foreignField: "film_id",
      as: "film"
    }
  },
  {
    $group: {
      _id: "$actor_id",
      titles: {$push: "$film.title"}
    }
  }
]);

I hope you find it useful.