Inner joins 2 tables in mongoose

user_4442606 0
Viewed 247

I'm trying to do an inner join that matches, but for some reason I get a left join. I have 2 relations tables, and I want to get the movie with the genre names.

Consider the following models:

// Movie
const MovieSchema = new mongoose.Schema({
    id: {
        type: Number,
        default: null,
        required: true,
        unique: true
    },
    title: {
        type: String,
        default: null,
        required: true,
        unique: true,
        trim: true
    },
});
const Movie = mongoose.model('Movie', MovieSchema);
module.exports = Movie;

// Genre
const GenreSchema = new mongoose.Schema({
    id: {
        type: Number,
        default: null,
        required: true,
        unique: true
    },
    name: {
        type: String,
        default: null,
        required: false,
        trim: true,
        unique: true
    }
});
const Genre = mongoose.model('Genre', GenreSchema);
module.exports = Genre;

// MovieGenre
const MovieGenreSchema = new mongoose.Schema({
    genreId: {
        type: Number,
        default: null,
        required: true
    },
    movieId: {
        type: Number,
        default: null,
        required: true
    }
});
const MovieGenre = mongoose.model('MovieGenre', MovieGenreSchema);
module.exports = MovieGenre;

I try to do the following query:

    {
        $lookup:
        {
            from: MovieGenre.collection.name,
            localField: 'id',
            foreignField: 'movieId',
            as: 'movieGenres'
        }
    },
    {
        $lookup: {
            from: Genre.collection.name,
            localField: 'g.id',
            foreignField: 'genreId',
            as: 'genreNames'
        }
    },
    {
        $match: {
            'genreNames.name': 'Action'
        }
    }

and I get the results:

{
  _id: 5ee9b51609f44c0f38262c94,
  id: 26583,
  title: 'The Keeper',
  __v: 0,
  movieGenres: [
    {
      _id: 5ee8b8cf0d186c20b4bf3ccd,
      genreId: 28,
      movieId: 26583,
      __v: 0
    },
    {
      _id: 5ee8b8cf0d186c20b4bf3cce,
      genreId: 53,
      movieId: 26583,
      __v: 0
    }
  ],
  genreNames: [
    { _id: 5ee8b68f0d186c20b4b03a3d, id: 35, name: 'Comedy', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a3e, id: 80, name: 'Crime', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a40, id: 18, name: 'Drama', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a42, id: 53, name: 'Thriller', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a43, id: 28, name: 'Action', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a45, id: 14, name: 'Fantasy', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a46, id: 27, name: 'Horror', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a4a, id: 10752, name: 'War', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a4b, id: 10402, name: 'Music', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a4c, id: 37, name: 'Western', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a4d, id: 36, name: 'History', __v: 0 }
  ]
}

but what i expected to get is:

{
  _id: 5ee9b51609f44c0f38262c94,
  id: 26583,
  title: 'The Keeper',
  __v: 0,
  movieGenres: [
    {
      _id: 5ee8b8cf0d186c20b4bf3ccd,
      genreId: 28,
      movieId: 26583,
      __v: 0
    },
    {
      _id: 5ee8b8cf0d186c20b4bf3cce,
      genreId: 53,
      movieId: 26583,
      __v: 0
    }
  ],
  genreNames: [
    { _id: 5ee8b68f0d186c20b4b03a43, id: 28, name: 'Action', __v: 0 },
    { _id: 5ee8b68f0d186c20b4b03a42, id: 53, name: 'Thriller', __v: 0 },
  ]
}

Can you please tell me, What am I doing wrong? Thanks.

mongoose node.js inner-join lookup javascript
1 Answers

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

user_89871280
Related