I have the following collections:

const movieSchema = new Schema({
  title: String
  ...
})

const userSchema = new Schema({
  firstName: String,
  lastName: String,
  movies: [
    movie: {
      type: Schema.Types.ObjectId,
      ref: 'Movie'
    },
    status: String,
    feeling: String
  ]
  ...
})

I am trying to match up the movie (with all its details) with the user status and feeling for that movie, with the aggregation:

Movie.aggregate([
  { $match: { _id: ObjectId(movieId) } },
  {
    $lookup: {
      from: 'users',
      as: 'user_status',
      pipeline: [
        { $match: { _id: ObjectId(userId) } },
        {
          $project: {
            _id: 0,
            movies: 1
          }
        },
        { $unwind: '$movies' }
      ]
    }
  },
  
])

Which returns:

[
  {
    _id: 610b678702500b0646925542,
    title: 'The Shawshank Redemption',
    user_status: [
      {
        "movies": {
          "_id": "610b678702500b0646925542",
          "status": "watched",
          "feeling": "love"
        }
      },
      {
        "movies": {
          "_id": "610b678502500b0646923627",
          "status": "watched",
          "feeling": "like"
        }
      },
      {
        "movies": {
          "_id": "610b678502500b0646923637",
          "status": "watched",
          "feeling": "like"
        }
      },
    ]
  }
]

My desired result is to match the first movie in user_status to get the eventual final result:

[
  {
    _id: 610b678702500b0646925542,
    title: 'The Shawshank Redemption',
    status: "watched",
    feeling: "love"
  }
]

I thought the next step in my pipeline would be:

{
  $addFields: {
    user_status: {
      $filter: {
        input: '$user_status',
        cond: {
          $eq: ['$$this.movies._id', '$_id']
        }
      }
    }
  }
}

But it doesn't work - Not sure if this $addFields is correct, and one problem I know is that my first _id is an ObjectId and the second appears to be a string.

CodePudding user response：

If I understand correctly, you can $filter the user in the already existing $lookup pipeline, which will make things more simple later:

db.movies.aggregate([
  {$match: {_id: ObjectId(movieId)}},
  {
    $lookup: {
      from: "users",
      as: "user_status",
      pipeline: [
        {$match: {_id: ObjectId(userId)}},
        {$project: {
            movies: {
              $first: {
                $filter: {
                  input: "$movies",
                  cond: {$eq: ["$$this.movie", ObjectId(movieId)]}
                }
              }
            }
          }
        }
      ]
    }
  },
  {
    $project: {
      title: 1,
      feeling: {$first: "$user_status.movies.feeling"},
      status: {$first: "$user_status.movies.status"}
    }
  }
])

See how it works on the playground example