title: MongoDB Aggregation "group" with "max" field within a sub-array
body: <p>Using Compass initially, I then need to convert it into the PHP library.</p>
  <p>So far, I have a 1st stage that filters the documents on 2 fields using <code>$match</code>:</p>
  <ul>
  <li>comp.id (sub-document / array)</li>
  <li>playerId</li>
  </ul>
  <p>Code is:
  $match (from drop-down)</p>
  <pre><code>{
      &quot;comp.id&quot; : ObjectId('607019361c071256e4f0d0d5'),
      &quot;playerId&quot; : &quot;609d0993906429612483cea0&quot;
  }
  </code></pre>
  <p>This returns 2 documents.</p>
  <p>The document has a sub-array <code>holes</code>, for the holes played in a round of golf. This sub-array has fields (among others):</p>
  <ul>
  <li>holes.no</li>
  <li>holes.par</li>
  <li>holes.grossScore</li>
  <li>holes.nettPoints</li>
  </ul>
  <p>So each round has 1 document, with a <code>holes</code> sub-array of (typically) 18 array elements (holes), or 9 for half-round. A player will play each round multiple times - hence multiple documents.</p>
  <p>I would like to find the highest <code>holes.nettPoints</code> across the documents. I think I need to <code>$group</code> with <code>$max</code> on the <code>holes.nettPoints</code> field, so I would find the highest score for each hole across all rounds.</p>
  <p>I have tried this, but in Compass its says its not properly formatted:
  <code>$group</code> drop-down</p>
  <pre><code>{
    _id: holes.no,
    &quot;highest&quot;:
      { $max: &quot;$holes.nettPoints&quot; }
  }
  </code></pre>
  <p>&quot;highest&quot; can be any name I want?</p>
  <p>EDIT FOLLOWING PROVIDED ANSWER</p>
  <p>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.</p>
  <p>For the purposes of posterity, I ended up using the following aggregation:</p>
  <pre><code>[{$match: {
      &quot;comp.id&quot; : ObjectId('607019361c071256e4f0d0d5'),
      &quot;playerId&quot; : &quot;609d0993906429612483cea0&quot;,
      &quot;comp.courseId&quot; : &quot;608955aaabebbd503ba6e116&quot;
  }
  }, {$unwind: {
    path : &quot;$holes&quot;
  }}, {$group: {
    _id: &quot;$holes.no&quot;,
    hole: {
      $max: &quot;$holes&quot;
    }
  }}, {$sort: {
    &quot;hole&quot;: 1
  }}]
  </code></pre>
  <p>In PHP speak, it looks like:</p>
  <pre><code>    $match = [
          '$match' =&gt; [
              'comp.id' =&gt; new MongoDB\BSON\ObjectID( $compId ),
              'playerId' =&gt; $playerId,
              'comp.courseId' =&gt; $courseId
          ]
      ];
      
      $unwind = [
          '$unwind' =&gt; [
              'path' =&gt; '$holes'
          ]
      ];
      
      $group = [
          '$group' =&gt; [
              '_id' =&gt; '$holes.no',
              'hole' =&gt; [
                  '$max' =&gt; '$holes'
              ]
          ]
      ];
      
      $sort = [
          '$sort' =&gt; [
              'hole.no' =&gt; 1
          ]
      ];
      
      $cursor = $collection-&gt;aggregate([$match, $unwind, $group, $sort]);
  </code></pre>
  <p>It is not complete (looking at adding a <code>$sum</code> accumulator across the <code>courseId</code>, not individual documents), but answers the question posted.</p>