Create a leader board from score entries-CodePudding

[{
    score: 7000,
    mapId: 1,
    profileId: 12
}, {
    score: 400,
    mapId: 1,
    profileId: 6
}, {
    score: 12000,
    mapId: 1,
    profileId: 12
}, {
    score: 5000,
    mapId: 1,
    profileId: 12
}, {
    score: 120,
    mapId: 1,
    profileId: 8
}]

I have a game's REST API and users send their map scores to a route and I save them in Mongo, users can have multiple score entries for multiple mapIds or for one mapId.

JSON above is a result of all documents, with score, mapId and profileId. You can see a profileId can have multiple scores for a mapId, I'm asking the best query for getting the top 3 best scores, but there cant be scores from multiple profileIds, which means it must be a profile's highest score only. The result should look like this:

[{
    score: 12000,
    mapId: 1,
    profileId: 12
}, {
    score: 400,
    mapId: 1,
    profileId: 6
}, {
    score: 120,
    mapId: 1,
    profileId: 8
}]

I have tried following aggregation to get mapId: 1 leaderboard but scores can get mixed up and the lowest score weirdly sometimes turns into the highest score.

this.db.aggregate([
     { $match: { mapId: 1 } },
     { $group: { _id: "$profileId", score: { $max: "$score" }, root: { $first: "$$ROOT" } } },
     { $sort: { score: -1 } },
     { $limit: 3 }
]);

I'm looking for something that does it all easy without anything extra or dumb.

CodePudding user response：

To be honest your query is already good enough i think. You should remove the root : {$first :"$$ROOT"} because the $first operator won't always give you the doc with the highest score.

this.db.aggregate([
  {
    $match: {
      mapId: 1
    }
  },
  {
    $group: {
      _id: "$profileId",
      score: {
        $max: "$score"
      },
      
    }
  },
  {
    $sort: {
      score: -1
    }
  },
  {
    $limit: 3
  },
  {
    $set: {
      profileId: "$_id",
      mapId: 1
    }
  },
  {
    $unset: "_id"
  }
])

You should add some $set and $unset stage for your results to match the expected output.

CodePudding user response：

You can use $setWindowFields to compute $rank for each profileId. You can then take rank: 1 to get the top score for each player. Finally, do a $sort $limit to get the top 3 scores.

db.collection.aggregate([
  {
    "$match": {
      mapId: 1
    }
  },
  {
    "$setWindowFields": {
      "partitionBy": "$profileId",
      "sortBy": {
        "score": -1
      },
      "output": {
        "rank": {
          $rank: {}
        }
      }
    }
  },
  {
    $match: {
      rank: 1
    }
  },
  {
    "$unset": "rank"
  },
  {
    "$sort": {
      score: -1
    }
  },
  {
    $limit: 3
  }
])

Mongo Playground

Thanks @Joe and @nimrod serok for the constructive feedbacks.