Calculating the sum of specific fields from a complex array object

Question

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.

turivishal · Accepted Answer · 2021-01-27 09:07:23Z

1

$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] } } }

edited Jan 27, 2021 at 9:07

answered Jan 27, 2021 at 8:27

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gibbs · Accepted Answer · 2021-01-27 08:27:49Z

1

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

answered Jan 27, 2021 at 8:27

Gibbs

23.1k14 gold badges84 silver badges148 bronze badges

1 Comment

Dimnox Over a year ago

Woah, that was quite fast. As turivishal's solution is closer to the expected structure I accepted that answer but I'm really thankful for your help.

Collectives™ on Stack Overflow

Calculating the sum of specific fields from a complex array object

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related