Search code examples
databasemongodbnosqlaggregation-frameworkaggregate

How to include $sum field for each existing field in a document with aggregate?


I have this document.

{
"_id": "1",
"name": "KP",
"points": 20
},
{
"_id": "2",
"name": "Josh",
"points: 30
}, 
{
"_id": "3",
"name": "Angelo",
"points": 50
}

How do I get the sum of all fields (should be 100) and then add them on each document?

{
"_id": "1",
"name": "KP",
"points": 20,
"total": 100
},
{
"_id": "2",
"name": "Josh",
"points": 30,
"total": 100
}, 
{
"_id": "3",
"name": "Angelo",
"points": 50,
"total": 100
}

I need this so I could divide and multiply later on because my plan really is to add a percent field, like a share on the total points (i.e. for 50 pts, (50 / 100) * 100 = 50%. Like this:

{
"_id": "1",
"name": "KP",
"points": 20,
"percentage": 20
},
{
"_id": "2",
"name": "Josh",
"points": 30,
"total": 30
}, 
{
"_id": "3",
"name": "Angelo",
"points": 50,
"percentage": 50
}

So far I have this which is incorrect:

db.users.aggregate({$match:{}}, {$project: {_id: 1, name: 1, points: "$points"}}, {$group: {_id: "$name", total: {$sum: "$points"}}})

Solution

  • Use $setWindowFields to compute the total with "partitionBy": null. Then use $divide and $multiply to compute the percentage.

    db.collection.aggregate([
      {
        "$setWindowFields": {
          "output": {
            "total": {
              "$sum": "$points"
            }
          }
        }
      },
      {
        $set: {
          percentage: {
            "$multiply": [
              {
                "$divide": [
                  "$points",
                  "$total"
                ]
              },
              100
            ]
          }
        }
      }
    ])
    

    Mongo Playground