Embedded document corrresponding to the maximum value in pymongo

Question

I have the following schema for documents in my collection. Each document corresponds to list all the submissions made by a name.

- "_id": ObjectId
- "name": str
- "is_team": bool
- "submissions": List
   - time: datetime
   - score: float

Example:

{"name": "Intrinsic Nubs",
  "is_team": true,
  "submissions": [
    {
      "score": 61.77466359705439,
      "time": {
        "$date": {
          "$numberLong": "1656009267652"
        }
      }
    },
      {
      "score": 81.77466359705439,
      "time": {
        "$date": {
          "$numberLong": "1656009267680"
        }
      }
    }]}

I need to collect all those documents whose is_team is True and further get the name, Maximum Score and time corresponding to the maximum score.

Example:

[{"name": "Intrinsic Nubs", "MaxScore": 81.77466359705439, "time":{ "$date": {"$numberLong": "1656009267680"}}}]

Answer 1

Here's another way to produce your desired output.

db.collection.aggregate([
  { // limit docs
    "$match": {"is_team": true}
  },
  { // set MaxScore
    "$set": {"MaxScore": {"$max": "$submissions.score"}}
  },
  { "$project": {
      "_id": 0,
      "name": 1,
      "MaxScore": 1,
      "time": {
        // get time at MaxScore
        "$arrayElemAt": [
          "$submissions.time",
          {"$indexOfArray": ["$submissions.score", "$MaxScore"]}
        ]
      }
    }
  }
])

Try it on mongoplayground.net.