MongoDB: Is there a way to compute sum on past records?

Question

Assume I have a dataset like :

yearMonth | amount
201908    | 100
201909    | 100
201910    | 200
201911    | 100
201912    | 200
202001    | 300
202002    | 200

Is there a way I can do a sum/accumulate on pass records to get a result set like :

yearMonth | amount | balance
201908    | 100    | 100
201909    | 100    | 200
201910    | 200    | 400
201911    | 100    | 500
201912    | 200    | 700
202001    | 300    | 1000
202002    | 200    | 1200

This [answer](https://stackoverflow.com/a/27995505/1935238) recommends [mapReduce](https://docs.mongodb.com/manual/reference/command/mapReduce/) over the aggregation framework for a running total. — DaveStSomeWhere, Apr 05 '20 at 01:59

score 0 · Answer 1 · answered Apr 05 '20 at 02:58

Try below aggregation query :

db.collection.aggregate([
   /** Sort on entire collection is not preferred, but you need it if 'yearMonth' field is not ordered */
  /** Group on empty & push all docs to 'data' array */
  { $group: { _id: "", data: { $push: "$$ROOT" } } },
  {
    $project: {
      data: {
        $let: {
          vars: {
            data: {
              $reduce: {
                input: "$data", /** Iterate over 'data' array & push newly formed docs to docs array */
                initialValue: { amount: 0, docs: [] },
                in: {
                    docs: {
                    $concatArrays: [
                      "$$value.docs",
                      [
                        {
                          _id: "$$this._id",
                          yearMonth: "$$this.yearMonth",
                          amount: "$$this.amount",
                          balance: {
                            $add: ["$$value.amount", "$$this.amount"],
                          },
                        },
                      ],
                    ],
                  },
                  amount: { $add: ["$$value.amount", "$$this.amount"] },
                },
              },
            },
          },
          in: "$$data.docs", /** Return only 'docs' array & ignore 'amount' field */
        },
      },
    },
  },
  /** unwind 'data' array(newly formed 'data' array field) */
  {
    $unwind: "$data",
  },
  /** Replace data object as new root for each document in collection  */
  {
    $replaceRoot: {
      newRoot: "$data",
    },
  },
]);

Test : MongoDB-Playground Ref : aggregation-pipeline-operators

DaveStSomeWhere · Answer 2 · 2020-04-06T01:25:27.483

Using the mapReduce collection method with guidance from this answer you can get your desired results.

Here's a pymongo solution using the following options:

map function - this does the initial mapping of the key, value pair to be emitted (the yearMonth and Amount).
reduce function - didn't need any action for this case.
out - specifies where to put the output - could be a collection or as in this case just processed inline.
scope - specifies the rolling total field - just called total
finalize - this does the actual totaling.

Here's the python(pymongo) code:

from pymongo import MongoClient
from bson.code import Code

client = MongoClient()
db = client.tst1
coll = db.mapr1

map1 = Code('''
        function () {
            emit(
                this.yearMonth, 
                this.amount              
                ); 
            }
    ''')

reduce1 = Code('''
    function (key, values) {
        return value;
               }
               ''')

fin1 = Code('''
            function(key, value) {
                total += value;
                return {amount: value, balance: total};
            }
''')

result = coll.map_reduce(map1, reduce1, out={'inline': 1}, scope={'total': 0}, finalize=fin1)

for doc in result['results']:
    print(f'The doc is {doc}')

Results:

The doc is {'_id': 201908.0, 'value': {'amount': 100.0, 'balance': 100.0}}
The doc is {'_id': 201909.0, 'value': {'amount': 100.0, 'balance': 200.0}}
The doc is {'_id': 201910.0, 'value': {'amount': 200.0, 'balance': 400.0}}
The doc is {'_id': 201911.0, 'value': {'amount': 100.0, 'balance': 500.0}}
The doc is {'_id': 201912.0, 'value': {'amount': 200.0, 'balance': 700.0}}
The doc is {'_id': 202001.0, 'value': {'amount': 300.0, 'balance': 1000.0}}
The doc is {'_id': 202002.0, 'value': {'amount': 200.0, 'balance': 1200.0}}

Documents in collection:

{'_id': ObjectId('5e89c410b187b1e1abb089af'),
 'amount': 100,
 'yearMonth': 201908}
{'_id': ObjectId('5e89c410b187b1e1abb089b0'),
 'amount': 100,
 'yearMonth': 201909}
{'_id': ObjectId('5e89c410b187b1e1abb089b1'),
 'amount': 200,
 'yearMonth': 201910}
{'_id': ObjectId('5e89c410b187b1e1abb089b2'),
 'amount': 100,
 'yearMonth': 201911}
{'_id': ObjectId('5e89c410b187b1e1abb089b3'),
 'amount': 200,
 'yearMonth': 201912}
{'_id': ObjectId('5e89c410b187b1e1abb089b4'),
 'amount': 300,
 'yearMonth': 202001}
{'_id': ObjectId('5e89c410b187b1e1abb089b5'),
 'amount': 200,
 'yearMonth': 202002}

MongoDB: Is there a way to compute sum on past records?

2 Answers2