Mongoose - projection with $elemMatch on nested fields-CodePudding

I'm relatively new to MongoDB/Mongoose and I've only performed simple queries. Now I'm having some trouble trying to filter my database in a slightly more complex way. I already did some research to tackle my previous issues, but now I can't move forward. Here's what happening:

This is my schema:

const userSchema = new mongoose.Schema({
  email: String,
  password: String,
  movies: [
    {
      title: String,
      movieId: Number,
      view_count: Number,
      rating: Number,
      review: String,
    },
  ],
  lists: {
    watched_movies: [
      {
        title: String,
        director: String,
        genres: [{ type: String }],
        runtime: Number,
        date: Date,
      },
    ],
  },
});

I want to make a GET request that matches simultaneously "lists.watched_movies": { _id: req.params.entryId } and also "movies.title": req.body.title for a given email, so that the outcome of the findOne query would be just those elements and not the whole document. What I'm trying to accomplish is something like that:

{
  email: "[email protected]",
  movies: [
    {
      title: "Mongoose Strikes Back",
      movieId: 123,
      view_count: 1,
      rating: 3,
      review: "Very confusing movie!"
    }
  ],
  lists: {
    watched_movies: [
      {
        _id: 4321
        title: "Mongoose Strikes Back",
        director: "Mongo",
        genres: ["Drama"],
        runtime: 150,
        date: "2021-11-22"
      }
    ]
  }
}

My first attempt to tackle it, however, wasn't successful. Here's what I tried:

router.route("/:entryId").get((req, res) => {
  User.findOne(
    { email: "[email protected]" },
    {
      "lists.watched_movies": { $elemMatch: { _id: req.params.entryId } },
      movies: { $elemMatch: { title: req.body.title } },
    },
    (err, entry) => {
      if (!err) {
        res.send(entry);
        console.log(entry);
      } else {
        console.log(err);
      }
    }
  );
});

It says that Cannot use $elemMatch projection on a nested field. I thought that maybe I can solve it by changing my schema, but I'd like to avoid it if possible.

CodePudding user response：

For your scenario, you can use $filter to filter document(s) in nested array field.

db.collection.find({
  email: "[email protected]"
},
{
  "lists.watched_movies": {
    "$filter": {
      "input": "$lists.watched_movies",
      "cond": {
        "$eq": [
          "$$this._id",
          4321// req.params.entryId
        ]
      }
    }
  },
  movies: {
    $elemMatch: {
      title: "Mongoose Strikes Back"// req.body.title
    }
  }
})

Sample Mongo Playground