mongoDB: perform collection.find() on calculated vales in documents

user_14206562 0
Viewed 58

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!

aggregation-framework mongodb-query mongodb python database
1 Answers

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" } }
  }

https://mongoplayground.net/p/d8xhOHYV4kn

user_22242650
Related