Inner joins 2 tables in mongoose

You just need to correct your second lookup with genre collection, i have added 2 approaches you can use anyone,

1) Using your approach:

• localField pass previous lookup result's movieGenres.genreId
• foreignField pass id of genre collection

  {    $lookup: {      from: Genre.collection.name,      localField: "movieGenres.genreId",      foreignField: "id",      as: "genreNames"    }  }

if you want to filter the genreNames names from above lookup by name,

• $filter to iterate loop of genreNames array and filter by name: Action

  {    $addFields: {      genreNames: {        $filter: {          input: "$genreNames",          cond: { $eq: ["$$this.name", "Action"] }        }      }    }  }

Your final query would be,

  {    $lookup: {      from: MovieGenre.collection.name,      localField: "id",      foreignField: "movieId",      as: "movieGenres"    }  },  {    $lookup: {      from: Genre.collection.name,      localField: "movieGenres.genreId",      foreignField: "id",      as: "genreNames"    }  },  {    $match: {      "genreNames.name": "Action"    }  },  {    $addFields: {      genreNames: {        $filter: {          input: "$genreNames",          cond: { $eq: ["$$this.name", "Action"] }        }      }    }  }

Playground

2) Using lookup with pipeline approach:

The alternate way to do this using lookup with pipeline,

• let to pass movieGenres.genreId from above lookup
• $match to match genreId using $expr expression match and name field and combine conditions using $and operations

  {    $lookup: {      from: MovieGenre.collection.name,      localField: "id",      foreignField: "movieId",      as: "movieGenres"    }  },  {    $lookup: {      from: Genre.collection.name,      let: { genreIds: "$movieGenres.genreId" },      pipeline: [        {          $match: {            $and: [              { $expr: { $in: ["$id", "$$genreIds"] } },              { name: "Action" }            ]          }        }      ],      as: "genreNames"    }  }

Playground