Retrieve highest score for each game using aggregate in MongoDB

Question

I am working on a database of various games and i want to design a query that returns top scorer from each game with specific player details.

The document structure is as follows:

db.gaming_system.insertMany(
    [
        {
            "_id": "01",
            "name": "GTA 5",
            "high_scores": [
                {
                    "hs_id": 1,
                    "name": "Harry",
                    "score": 6969
                },
                {
                    "hs_id": 2,
                    "name": "Simon",
                    "score": 8574
                },
                {
                    "hs_id": 3,
                    "name": "Ethan",
                    "score": 4261
                }
            ]
        },
        {
            "_id": "02",
            "name": "Among Us",
            "high_scores": [
                {
                    "hs_id": 1,
                    "name": "Harry",
                    "score": 926
                },
                {
                    "hs_id": 2,
                    "name": "Simon",
                    "score": 741
                },
                {
                    "hs_id": 3,
                    "name": "Ethan",
                    "score": 841
                }
            ]
        }
    ]
)

I have created a query using aggregate which returns the name of game and the highest score for that game as follows

db.gaming_system.aggregate(
    { "$project": { "maximumscore": { "$max": "$high_scores.score" }, name:1 } }, 
    { "$group": { "_id": "$_id", Name: { $first: "$name" }, "Highest_Score": { "$max": "$maximumscore" } } },
    { "$sort" : { "_id":1 } }
)

The output from my query is as follows:

{ "_id" : "01", "Name" : "GTA 5", "Highest_Score" : 8574 }
{ "_id" : "02", "Name" : "Among Us", "Highest_Score" : 926 }

I want to generate output which also provides the name of player and "hs_id" of that player who has the highest score for each game as follows:

{ "_id" : "01", "Name" : "GTA 5", "Top_Scorer" : "Simon", "hs_id": 2, "Highest_Score" : 8574 }
{ "_id" : "02", "Name" : "Among Us", "Top_Scorer" : "Harry", "hs_id": 1, "Highest_Score" : 926 }

What should be added to my query using aggregate pipeline?

Answer 1

[
  {
    $unwind: "$high_scores" //unwind the high_scores, so you can then sort
  },
  {
    $sort: {
      "high_scores.score": -1 //sort the high_scores, irrelevant of game, because we are going to group in next stage
    }
  },
  {
//now group them by _id, take the name and top scorer from $first (which is the first in that group as sorted by score in descending order

    $group: {
      _id: "$_id",
      name: {
        $first: "$name"
      },
      Top_Scorer: {
        $first: "$high_scores"
      }
    }
  }
]