Mongodb group by players-CodePudding

I have a API that saves gameplay data in a mongodb. that accepts gameID, userID and score

{
  "gameId": "G1",
  "userId": "U22",
  "score": 420
},
{
  "gameId": "G2",
  "userId": "U22",
  "score": 10
},
{
  "gameId": "G1",
  "userId": "U22",
  "score": 80
},
{
  "gameId": "G1",
  "userId": "U25",
  "score": 400
}

when 'Game Over' game score document creates. so users can play many times in a same day.

now I can get game leaderboard like below.

db.plays.aggrigate([
 {
  _id: '$userId',
  played: {
    $sum:1
  },
  score:{
    $sum: '$score'
  }
},
{
    $limit: 100
}, {
    $sort: {
        score: -1
    }
}
]);

output

{
    _id: U22,
    played:3,
    score:510
},
{
    _id: U25,
    played:1,
    score:100
}

now I wanna get array of played games.

{
    _id: U22,
    played:[
        {
            gameId: G1,
            score: 500
        },
        {
            gameId: G2,
            score: 10
        }
    ],
    score:500
},
{
    _id: U25,
    played:[
        {
            gameId: G1,
            score: 400
        }
    ],
    score:100
},

how can I achieve this. thanks in advance.

CodePudding user response：

One option is to group twice, first by both userId and gameId and then just by userId:

db.collection.aggregate([
  {$group: {
      _id: {game: "$gameId", user: "$userId"},
      score: {$sum: "$score"}
  }},
  {$group: {
      _id: "$_id.user",
      played: {$push: {game: "$_id.game", score: "$score"}},
      score: {$max: "$score"}
  }},
  {$sort: {score: -1}},
  {$limit: 100}
])

See how it works on the playground example