Say I have the following documents in a collection:
{
"_id" : ObjectId("111e2133c57a1d6111111111"),
"scores" : [
{
"date" : ISODate("2021-03-29T05:00:20.965Z"),
"test 1 points" : 50,
"test 2 points" : 10,
"total possible points" : 100
}
]
}
{
"_id" : ObjectId("111e2133c57a1d6111111222"),
"scores" : [
{
"date" : ISODate("2021-03-27T05:00:20.965Z"),
"test 1 points" : 30,
"test 2 points" : 20,
"total possible points" : 200
}
]
}
If I want to find all the documents which have ("test 1 points" + "test 2 points") / "total possible points" greater than .50, is there a way to do this efficiently with the MongoDB find() command?
Currently, I am using the inefficient method of looping through each document and performing my calculations, then recording the ObjectId if the calculated value is greater than .50.
Thanks in advance!
Demo - https://mongoplayground.net/p/c19Xx9-Ahb-
$unwind the scores to get into individual documents, use $add $divide with $gt to validEntry as true or false.
and $match documents back
db.collection.aggregate([
{ $unwind: "$scores" },
{
$addFields: {
validEntry: {
$gt: [
{ $divide: [
{ $add: [ "$scores.test 1 points", "$scores.test 2 points" ] },
"$scores.total possible points"
]
},
0.5]
}
}
},
{ $match: { validEntry: true } }
])
if you want to get the original document back $group them by _id and $push scores back to the array.
{
$group: { _id: "_id", scores: { $push: "$scores" } }
}