CODEWITHSUNDEEP
phpmongodb
jingo_man
jingo_man

Reputation: 529

MongoDB Aggregation combining both 'max' of EACH document and 'sum' of ALL documents

I am recording golf scores and now trying to calculate the leaderboards. This is for use on a PHP site, so I also need to translate the pipeline code (afterwards!).

Each round document has a sub-array of holes:

{
  "playerName": "Tiger Woods", 
  "comp": {
    "name": "US Open 2021", 
    "courseId": "608952e3abebbd503ba6e115", 
    "scoringMethod": "Stableford", 
    "tees": "Blue", 
    "courseName": "MAGC Middle 9", 
    "roundNo": 2, 
    "id": {
      "$oid": "607019361c071256e4f0d0d5"
    }
  }, 
  "holes": [
    {
      "blue": 431, 
      "holeNett": 4, 
      "par": 4, 
      "holeGross": 5, 
      "no": 1, 
      "holeNettPoints": 2, 
      "yellow": 420, 
      "si": 2, 
      "holeStrokes": 1, 
      "white": 444, 
      "red": 399
    }, 
    {
      "blue": 335, 
      "holeNett": 5, 
      "par": 4, 
      "holeGross": 5, 
      "no": 2, 
      "holeNettPoints": 1, 
      "yellow": 320, 
      "si": 8, 
      "holeStrokes": 0, 
      "white": 350, 
      "red": 295
    }, 
    {
      "blue": 385, 
      "holeNett": 4, 
      "par": 4, 
      "holeGross": 5, 
      "no": 3, 
      "holeNettPoints": 2, 
      "yellow": 362, 
      "si": 4, 
      "holeStrokes": 1, 
      "white": 400, 
      "red": 343
    }, 
    {
      "blue": 161, 
      "holeNett": 3, 
      "par": 3, 
      "holeGross": 3, 
      "no": 4, 
      "holeNettPoints": 2, 
      "yellow": 144, 
      "si": 7, 
      "holeStrokes": 0, 
      "white": 180, 
      "red": 105
    }, 
    {
      "blue": 461, 
      "holeNett": 5, 
      "par": 5, 
      "holeGross": 6, 
      "no": 5, 
      "holeNettPoints": 2, 
      "yellow": 439, 
      "si": 1, 
      "holeStrokes": 1, 
      "white": 473, 
      "red": 418
    }, 
    {
      "blue": 330, 
      "holeNett": 4, 
      "par": 4, 
      "holeGross": 4, 
      "no": 6, 
      "holeNettPoints": 2, 
      "yellow": 300, 
      "si": 9, 
      "holeStrokes": 0, 
      "white": 337, 
      "red": 281
    }, 
    {
      "blue": 381, 
      "holeNett": 6, 
      "par": 4, 
      "holeGross": 6, 
      "no": 7, 
      "holeNettPoints": 0, 
      "yellow": 363, 
      "si": 5, 
      "holeStrokes": 0, 
      "white": 390, 
      "red": 290
    }, 
    {
      "blue": 152, 
      "holeNett": 3, 
      "par": 3, 
      "holeGross": 3, 
      "no": 8, 
      "holeNettPoints": 2, 
      "yellow": 140, 
      "si": 6, 
      "holeStrokes": 0, 
      "white": 167, 
      "red": 131
    }, 
    {
      "blue": 366, 
      "holeNett": 3, 
      "par": 4, 
      "holeGross": 4, 
      "no": 9, 
      "holeNettPoints": 3, 
      "yellow": 344, 
      "si": 3, 
      "holeStrokes": 1, 
      "white": 396, 
      "red": 327
    }
  ], 
  "playerId": "609d0993906429612483cea0", 
  "holeCount": 9, 
  "countbackScores": [
    16, 
    11, 
    5, 
    2
  ], 
  "team": "magc", 
  "date": "2021-05-20T23:00:00+00:00", 
  "computedScore": 16, 
  "computedThru": 9, 
  "_id": {
    "$oid": "60a6aa828078924a6065dc9e"
  }
}

I have an aggregation pipeline that finds the highest or lowest (competition rules dependent) score on every hole. I had to $unwind the sub-array, producing a pipeline document per hole played. Typically this means either 18 documents or 9 for a half-round, per player. Also to note, each round could be played on a different comp.courseId (there are 3 courses in my initial comp) - so these need to be grouped by as well, hence it is currently in my $match filter.

Can I in the same pipeline calculate the cumulative total of final pipeline output, at the same time as retaining the previous documents?

[{$match: {
    "comp.id" : ObjectId('600019361c071256e4f0d0d5'),
    "playerId" : "600d0993906429612483cea0",
    "comp.courseId" : "600955aaabebbd503ba6e116"
}
}, 
{$unwind: {
  path : "$holes"
}}, 
{$group: {
  _id: "$holes.no",
  hole: {
    $max: "$holes"
  }
}}, 
{$set: {
  "total": { "$sum" : "$hole.holeNettPoints" }
}}, 
{$sort: {
  "hole.no": 1
}
}]

The $set pipeline is the stage I am testing at. But this only sums the holeNettPoints field, from a single document, so is the same as this value in each document.

I don't think I can do another $group of all these documents, as on my PHP page though, I need the other per-hole fields to build an HTML table. But its sorted by the cumulative total for the round. So I have tried adding a field with $set. Is this correct?

This returns a document per hole played (9 with my current data, commonly 18 for a full round of golf). I would like to have the cumulative holeNettPoints total for all 9 holes as well as the $max per hole holeNettPoints across each player on each courseId. Something of the structure:

playerId = 123456
courseId = abcdef
total = 18
holes = (
    ([no] => 1, [holeNettPoints] => 2),
    ([no] => 2, [holeNettPoints] => 2),
    ([no] => 3, [holeNettPoints] => 3),
    ([no] => 4, [holeNettPoints] => 1),
    ([no] => 5, [holeNettPoints] => 2),
    ([no] => 6, [holeNettPoints] => 2),
    ([no] => 7, [holeNettPoints] => 2),
    ([no] => 8, [holeNettPoints] => 2),
    ([no] => 9, [holeNettPoints] => 2),
)

Ideally, the result would return a single document, and then I would be able to remove the playerId $match filter so that it would work across the many players who would play in each comp.

I would prefer that MongoDB uses aggregation to return the formatted and sorted result, so PHP can just loop through it's results.

Upvotes: 1

Views: 119

Answers (1)

turivishal
turivishal

Reputation: 36114

  • $group by playerId, courseId and no and get max of holeNettPoints
  • $group by playerId and construct the array of holes with no and holeNettPoints and also get total of all holeNettPoints
db.collection.aggregate([
  {
    $match: {
      "comp.id": ObjectId("607019361c071256e4f0d0d5"),
      "playerId": "609d0993906429612483cea0",
      "comp.courseId": "608952e3abebbd503ba6e115"
    }
  },
  { $unwind: { path: "$holes" } },
  {
    $group: {
      _id: {
        playerId: "$playerId",
        courseId: "$comp.courseId",
        no: "$holes.no"
      },
      holeNettPoints: { $max: "$holes.holeNettPoints" }
    }
  },
  {
    $group: {
      _id: "$_id.playerId",
      courseId: { $first: "$_id.courseId" },
      holes: {
        $push: {
          no: "$_id.no",
          holeNettPoints: "$holeNettPoints"
        }
      },
      total: { $sum: "$holeNettPoints" }
    }
  }
])

Playground

PHP syntax:

$query = [
  [
    '$match' => [
      'comp.id' => ObjectId("607019361c071256e4f0d0d5"),
      'playerId' => "609d0993906429612483cea0",
      'comp.courseId' => "608952e3abebbd503ba6e115"
    ]
  ],
  [ '$unwind' => [ 'path' => '$holes' ] ],
  [
    '$group' => [
      '_id' => [
        'playerId' => '$playerId',
        'courseId' => '$comp.courseId',
        'no' => '$holes.no'
      ],
      'holeNettPoints' => [ '$max' => '$holes.holeNettPoints' ]
    ]
  ],
  [
    '$group' => [
      '_id' => '$_id.playerId',
      'courseId' => [ '$first' => '$_id.courseId' ],
      'holes' => [
        '$push' => [
          'no' => '$_id.no',
          'holeNettPoints' => '$holeNettPoints'
        ]
      ],
      'total' => [ '$sum' => '$holeNettPoints' ]
    ]
  ]
]

Upvotes: 2

Related Questions