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

CodePudding user response：

Query

• keep documents with is_team=true
• reduce to find the member with the biggest score, and return it
• you can $project, futher i kept all to see the change

Playmongo

aggregate(
[{"$match": {"is_team": {"$eq": true}}},
 {"$set": 
   {"name": "$name",
    "max-submision": 
     {"$reduce": 
       {"input": "$submissions",
        "initialValue": {"score": 0},
        "in": 
         {"$cond": 
           [{"$gt": ["$$this.score", "$$value.score"]}, "$$this",
            "$$value"]}}}}}])

CodePudding user response：

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.