CODEWITHSUNDEEP
mongodbmongoosemongodb-query
Daywison Ferreira Leal
Daywison Ferreira Leal

Reputation: 13

How to group a collection to sum the properties of an object and generate a calculated field with MongoDB

how to group a collection by a specific field and generate a field calculated with the sum of the properties of an object with mongoDB, I don't know if it's possible to do what I'm trying to do but I would like to know.

I want to group by duelId field to add the score Collection:

  {  "matches": [{
      "secondParticipant": {"name": "Maria","battlesWon": 2},
      "firstParticipant": {"name": "Fabio","battlesWon": 1},
      "duelId": "6c3e532d-3c0e-4438-8289-c86a4a51a102",
    },{
      "secondParticipant": {"name": "Fabio","battlesWon": 1 },
      "firstParticipant": {"name": "Maria","battlesWon": 1 },
      "duelId": "6c3e532d-3c0e-4438-8289-c86a4a51a102"
    },
    {
      "secondParticipant": {"name": "Luiz","battlesWon": 1},
      "firstParticipant": {"name": "Jose","battlesWon": 1},
      "duelId": "6c3e532d-3c0e-4438-8289-c86a4a51a666"
    }]}

expected:

 {[ [
      {
        "secondParticipant": {"name": "Maria","battlesWon": 2 },
        "firstParticipant": {"name": "Fabio","battlesWon": 1 },
        "duelId": "6c3e532d-3c0e-4438-8289-c86a4a51a102",
      },{
        "secondParticipant": {"name": "Fabio","battlesWon": 1},
        "firstParticipant": {"name": "Maria","battlesWon": 1},
        "duelId": "6c3e532d-3c0e-4438-8289-c86a4a51a102"
      },
      "score": { "Fabio": 2, "Maria": 3 } *// this field would be the sum of battlesWon after grouping*
    ], 
    [
      {
        "secondParticipant": {"name": "Luiz","battlesWon": 1},
        "firstParticipant": {"name": "Jose","battlesWon": 1},
        "duelId": "6c3e532d-3c0e-4438-8289-c86a4a51a666"
      },
      "score": { "Luiz": 1, "Jose": 1 } // *this field would be the sum of battlesWon after grouping*
    ]]}

Upvotes: 1

Views: 163

Answers (1)

ray
ray

Reputation: 15257

You can do the following:

  1. $unwind the matches array
  2. rename firstParticipant and secondParticipant into an array of k-v tuple for further processing
  3. group by duel and name to sum up the score of each participant
  4. group by duel to get the requested name-score tuple for each duel
  5. use $arrayToObject to convert the k-v tuple array in step 2 back to the requested form.
db.collection.aggregate([
  {
    "$unwind": "$matches"
  },
  {
    "$addFields": {
      "matches.participants": [
        {
          "k": "$matches.firstParticipant.name",
          "v": "$matches.firstParticipant.battlesWon"
        },
        {
          "k": "$matches.secondParticipant.name",
          "v": "$matches.secondParticipant.battlesWon"
        }
      ]
    }
  },
  {
    "$unwind": "$matches.participants"
  },
  {
    "$group": {
      "_id": {
        duel: "$matches.duelId",
        name: "$matches.participants.k"
      },
      "matches": {
        "$push": {
          duelId: "$matches.duelId",
          firstParticipant: "$matches.firstParticipant",
          secondParticipant: "$matches.secondParticipant"
        }
      },
      score: {
        $sum: "$matches.participants.v"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.duel",
      "matches": {
        "$first": "$matches"
      },
      "score": {
        $push: {
          k: "$_id.name",
          v: "$score"
        }
      }
    }
  },
  {
    "$addFields": {
      "score": {
        "$arrayToObject": "$score"
      }
    }
  }
])

Here is the Mongo Playground for your reference.

Upvotes: 2

Related Questions