MongoDB Aggregation “group” with “max” field within a sub-array

Using Compass initially, I then need to convert it into the PHP library.

So far, I have a 1st stage that filters the documents on 2 fields using $match:

• comp.id (sub-document / array)
• playerId

Code is: $match (from drop-down)

{
    "comp.id" : ObjectId('607019361c071256e4f0d0d5'),
    "playerId" : "609d0993906429612483cea0"
}

This returns 2 documents.

The document has a sub-array holes, for the holes played in a round of golf. This sub-array has fields (among others):

• holes.no
• holes.par
• holes.grossScore
• holes.nettPoints

So each round has 1 document, with a holes sub-array of (typically) 18 array elements (holes), or 9 for half-round. A player will play each round multiple times – hence multiple documents.

I would like to find the highest holes.nettPoints across the documents. I think I need to $group with $max on the holes.nettPoints field, so I would find the highest score for each hole across all rounds.

I have tried this, but in Compass its says its not properly formatted: $group drop-down

{
  _id: holes.no,
  "highest":
    { $max: "$holes.nettPoints" }
}

“highest” can be any name I want?

EDIT FOLLOWING PROVIDED ANSWER

The answer marked as the solution was enough of a pointer for how the Aggregation Framework operates (multi-stage documents, i.e. documents as input to 1 stage become new documents as the output of that stage. And so on.

For the purposes of posterity, I ended up using the following aggregation:

[{$match: {
    "comp.id" : ObjectId('607019361c071256e4f0d0d5'),
    "playerId" : "609d0993906429612483cea0",
    "comp.courseId" : "608955aaabebbd503ba6e116"
}
}, {$unwind: {
  path : "$holes"
}}, {$group: {
  _id: "$holes.no",
  hole: {
    $max: "$holes"
  }
}}, {$sort: {
  "hole": 1
}}]

In PHP speak, it looks like:

    $match = [
        '$match' => [
            'comp.id' => new MongoDBBSONObjectID( $compId ),
            'playerId' => $playerId,
            'comp.courseId' => $courseId
        ]
    ];
    
    $unwind = [
        '$unwind' => [
            'path' => '$holes'
        ]
    ];
    
    $group = [
        '$group' => [
            '_id' => '$holes.no',
            'hole' => [
                '$max' => '$holes'
            ]
        ]
    ];
    
    $sort = [
        '$sort' => [
            'hole.no' => 1
        ]
    ];
    
    $cursor = $collection->aggregate([$match, $unwind, $group, $sort]);

It is not complete (looking at adding a $sum accumulator across the courseId, not individual documents), but answers the question posted.

Answer

• $match your conditions
• $unwind deconstruct holes array
• $sort by nettPoints in descending order
• $group by no and select first holes object

[
  {
    $match: {
      "comp.id": ObjectId("607019361c071256e4f0d0d5"),
      "playerId": "609d0993906429612483cea0"
    }
  },
  { $unwind: "$holes" },
  { $sort: { "holes.nettPoints": -1 } },
  {
    $group: {
      _id: "$holes.no",
      highest: { $first: "$holes" }
    }
  }
]