I have data like this:

{
  "_id": "n383hopmfz69j7rdbh1ny56g",
  "playerId": "402ddb96-0edf-4df7-9ba8-ffb201ef6a70",
  "playedChartId": "3w2aaz0ryd58it6r3w0klu5r",
  "score": 966858
},
{
  "_id": "5e83ecd30102b60ea4a11fe9",
  "playerId": "402ddb96-0edf-4df7-9ba8-ffb201ef6a70", // the same player
  "playedChartId": "3w2aaz0ryd58it6r3w0klu5r",
  "score": 954201 // with lower score
},
{
  "_id": "629da76873dff6547eb1e9b3",
  "playerId": "f6fe9c4d-98e6-450a-937c-d64848eacc40", // the different player
  "playedChartId": "3w2aaz0ryd58it6r3w0klu5r", // the same chart
  "score": 902238
}

As I know how to filter 'playedChartId', so I ignored the other documents with different 'playedChartId'.

Then I want to group them by 'playerId', find the document with the best 'score', with 'ranking'.

The result I wanted:

[
  {
    // the original document
    "_id": "n383hopmfz69j7rdbh1ny56g",
    "playerId": "402ddb96-0edf-4df7-9ba8-ffb201ef6a70",
    "playedChartId": "3w2aaz0ryd58it6r3w0klu5r",
    "score": 966858,

    // the generated ranking value
    "ranking": 1
  },
  {
    "_id": "n383hopmfz69j7rdbh1ny56g",
    "playerId": "f6fe9c4d-98e6-450a-937c-d64848eacc40", // the different player
    "playedChartId": "3w2aaz0ryd58it6r3w0klu5r", // the same chart
    "score": 902238,

    "ranking": 2
  }
]

CodePudding user response：

1. $sort - Order by playerId then by score descending.

2. $group - Group by playerId, and take the first document as data via $first.

3. $replaceWith - Replace input document with data.

4. $setWindowFields - Perform ranking via $rank order by score descending.

db.collection.aggregate([
  {
    $sort: {
      playerId: 1,
      score: -1
    }
  },
  {
    $group: {
      _id: "$playerId",
      data: {
        $first: "$$ROOT"
      }
    }
  },
  {
    $replaceWith: "$data"
  },
  {
    $setWindowFields: {
      sortBy: {
        score: -1
      },
      output: {
        ranking: {
          $rank: {}
        }
      }
    }
  }
])

Sample Mongo Playground