CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Dimnox
Dimnox

Reputation: 471

Calculating the sum of specific fields from a complex array object

I would like to migrate one of my FireBase projects to Mongo and move the calculations from server side to DB. I already wrote most of the queries but this one is beyond my knowledge. Player data are saved by week and I need to calculate the sum of donations and points for each players (the rest of the fields should be ignored).

PS: Some of the players are already banned so it would be enough the calculate the fields for a given player set (like: tag in ['playerId1', 'playerId2', ...]). If it's too complex I will do this filtering later on server side.

[
   {
      "week":"2021-01",
      "players":[
         {
            "donations":20,
            "games":3,
            "name":"Player1",
            "points":258,
            "tag":"playerId1"
         },
         {
            "donations":37,
            "games":5,
            "name":"Player2",
            "points":634,
            "tag":"playerId2"
         },
         { ... }
      ]
   },
   {
      "week":"2021-02",
      "players":[ { ... } ]
   }
]

So the result should be something like this:

[
   {
      "name":"Player1",
      "tag":"playerId1",
      "donations":90,
      "points":980
   },
   {
      "name":"Player2",
      "tag":"playerId2",
      "donations":80,
      "points":1211
   }
]

I think the $unwind and the $group operators could be the key but I can't figure out how to use them properly here.

Upvotes: 0

Views: 51

Answers (2)

turivishal
turivishal

Reputation: 36144

  • $unwind deconstruct players array
  • $group by name and get sum of donations and points and get first tag
  • $project to show required fields
db.collection.aggregate([
  { $unwind: "$players" },
  {
    $group: {
      _id: "$players.name",
      donations: { $sum: "$players.donations" },
      points: { $sum: "$players.points" },
      tag: { $first: "$players.tag" }
    }
  },
  {
    $project: {
      _id: 0,
      name: "$_id",
      points: 1,
      tag: 1,
      donations: 1
    }
  }
])

Playground

PS: Some of the players are already banned so it would be enough the calculate the fields for a given player set (like: tag in ['playerId1', 'playerId2', ...]).

You can put match condition after $unwind stage,

{ $match: { "players.tag": { $in: ['playerId1', 'playerId2', ..more] } } }

Upvotes: 1

Gibbs
Gibbs

Reputation: 22974

You were right,

play

db.collection.aggregate([
  {//Denormalize
    "$unwind": "$players"
  },
  {//Group by name
    "$group": {
      "_id": "$players.name",
      "donations": {
        "$sum": "$players.donations"
      },
      "points": {
        "$sum": "$players.points"
      },
      
    }
  }
])

You can add project stage if you really need name as key than _id

Upvotes: 1

Related Questions