Mongoose aggregation pipeline: Comparing to own object

I have a sample mongoose object that looks like this:

  {
    _id: 5fa849ad4f6be0382363809c,
    ratings: {
      ratedPersonId: 7,
      rating: 7,
      timeSpent: 30,
      timestamp: 78,
      userThreshold: 5
    }
  },

it contains an _id and a list of ratings which is a subdocument with the following features.

I have created an aggregation pipeline like this:

    const ratedUser = await this.ratingModel
      .aggregate([
        { $project: { ratings: 1 } },
        { $unwind: '$ratings' },
        {
          $match: {
            $and: [{ 'ratings.ratedPersonId': userId }, { 'ratings.rating': { $gte: 5 } }],
          },
        },
      ])
      .exec()

This works for the first condition ratings.ratedPersonId: userId

My problem is that my second condition is the rating should be greater than or equal to the userThreshold field in the same object.

whenever I type that in the query it returns nothing

 $and: [{ 'ratings.ratedPersonId': userId }, { 'ratings.rating': { $gte: 'ratings.threshold'} }],

Answers 1

  • Demo - https://mongoplayground.net/p/AQMsJGkoFcu

    Use $expr to compare the fields

    Read aggregation-expressions

    $expr can build query expressions that compare fields from the same document in a $match stage. If the $match stage is part of a $lookup stage, $expr can compare fields using let variables. See Specify Multiple Join Conditions with $lookup for an example. $expr only uses indexes on the from the collection for equality matches in a $match stage. $expr does not support multikey indexes.

    db.collection.aggregate([
      {
        $project: {
          ratings: 1
        }
      },
      {
        $unwind: "$ratings"
      },
      {
        $match: {
          $and: [
            {
              "ratings.ratedPersonId": 7
            },
            {
              $expr: {
                $gte: [
                  "$ratings.rating",
                  "$ratings.userThreshold"
                ]
              }
            }
          ],
          
        },
      },
    ])
    

Related Articles