Return an array of movie objects by connecting two collections-CodePudding

I have a collection of User, Movie, and Wishlist.

A user is already stored in a variable user_email and I would like to get all the movie objects that have the same id as the one stored in wishlist. Here are some schemas and data types:

Sample:

user_email: [email protected]

Movie Movie Schema:

const movieSchema = mongoose.Schema({ 
    image: String, 
    title: String, 
    rating: String, 
    length: String, 
    // timeslots: Array
    timeslots: [{
        id: Number, 
        time: String, 
        seats: []
    }]
});

Movie Data Sample:

_id: 61d65a3431a314f4fc7e9170
image: "https://static.onecms.io/wp-content/uploads/sites/20/2021/08/25/spence..."
title: "Spencer"
rating: "R"
length: "1 hr 57 min"
timeslots: Array
__v: 0

Wishlist Wishlist Schema:

const wishlistSchema = mongoose.Schema({ 
    user: String, 
    movie: Schema.ObjectId
}

All Data in Wishlist:

_id: 61ea4e67984f981ee529c008
user: "[email protected]"
movie: 61d65a3431a314f4fc7e9170
__v: 0

_id: 61ecf6f6c7b93b96890c9ebb
user: "[email protected]"
movie: 61e3a7bed4f5306388103156
__v: 0

_id: 61ecf73c9f651a07079c9641
user: "[email protected]"
movie: 61e3acc21d84993477d89a22
__v: 0

_id: 61ecf73e9f651a07079c9644
user: "[email protected]"
movie: 61d65a3431a314f4fc7e9170
__v: 0

How do I get an array of all the movie objects that matches all the movies with the shangchi email in the Wishlist collection?

CodePudding user response：

With $lookup.

db.movie.aggregate([
  {
    $lookup: {
      from: "wishlist",
      let: {
        movieId: "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$movie",
                    "$$movieId"
                  ]
                },
                {
                  $eq: [
                    "$user",
                    "[email protected]"
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "wishlist"
    }
  }
])

Sample Mongo Playground

Reference

Perform Multiple Joins and a Correlated Subquery with $lookup

CodePudding user response：

Since you are using mongoose, you can perform a simple find query on Wishlist collection with populate on movie field.

const wishlistMovies = await Wishlist.find({ 
  user: "[email protected]" 
}).populate("movie", null, "Movie")

This will return a list of wishlist movies for user with email [email protected] where movie id will be replaced by the Movie document.