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'} }],
Demo - https://mongoplayground.net/p/AQMsJGkoFcu
Use $expr to compare the fields
$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"
]
}
}
],
},
},
])