How to calculate the total quantity using an aggregate

Question

I am developing a simple financial application for tracking income and results.

For simplicity, suppose these are some of my docs:

{ "_id" : ObjectId("54adc0659413535e02fba115"), "description" : "test1", "amount" : 100, "dateEntry" : ISODate("2015-01-07T23:00:00Z") } { "_id" : ObjectId("54adc21a0d150c760270f99c"), "description" : "test2", "amount" : 50, "dateEntry" : ISODate("2015-01-06T23:00:00Z") } { "_id" : ObjectId("54b05da766341e4802b785c0"), "description" : "test3", "amount" : 11, "dateEntry" : ISODate("2015-01-09T23:00:00Z") } { "_id" : ObjectId("54b05db066341e4802b785c1"), "description" : "test4", "amount" : 2, "dateEntry" : ISODate("2015-01-09T23:00:00Z") } { "_id" : ObjectId("54b05dbb66341e4802b785c2"), "description" : "test5", "amount" : 12, "dateEntry" : ISODate("2015-01-09T23:00:00Z") } { "_id" : ObjectId("54b05f4ee0933a5c02398d55"), "description" : "test6", "amount" : 4, "dateEntry" : ISODate("2015-01-09T23:00:00Z") } 

Now I would like to draw a “balance” based on such data:

 [ { day:'2015-01-06', amount:50}, { day:'2015-01-07', amount:150}, { day:'2015-01-09', amount:179}, ... ] 

In other words, I need to group all my transactions in the afternoon, and for each day I need to summarize all my previous transactions (from the very beginning of the world).

I already know how to group by day:

 $group: { _id: { y: {$year:"$dateEntry"}, m: {$month:"$dateEntry"}, d: {$dayOfMonth:"$dateEntry"} }, sum: ??? } 

But I do not know how to return and summarize all the amounts. Imagine that I need to show a monthly balance report: should I run 31 requests, one for each day, summing up the entire transaction amount, except for the following days? Of course I can, but I don't think the best solution.

Thanks in advance!

Answer 1

Actually more suitable for mapReduce than the aggregation structure, at least in the initial solution of problems. In the aggregation structure, there is no concept of the value of the previous document or the previous “grouped” value of the document, so it cannot do this.

On the other hand, mapReduce has a “global scope” that can be divided between steps and documents as they are processed. This will give you the “current amount” for the current balance at the end of the day that you need.

 db.collection.mapReduce( function () { var date = new Date(this.dateEntry.valueOf() - ( this.dateEntry.valueOf() % ( 1000 * 60 * 60 * 24 ) ) ); emit( date, this.amount ); }, function(key,values) { return Array.sum( values ); }, { "scope": { "total": 0 }, "finalize": function(key,value) { total += value; return total; }, "out": { "inline": 1 } } ) 

This will be summed up by the grouping date, and then in the “finalize” section it sums up the amount from each day.

  "results" : [ { "_id" : ISODate("2015-01-06T00:00:00Z"), "value" : 50 }, { "_id" : ISODate("2015-01-07T00:00:00Z"), "value" : 150 }, { "_id" : ISODate("2015-01-09T00:00:00Z"), "value" : 179 } ], 

In the long run, it is best for you to have a separate collection with a daily record to change the balance using $inc in the update. Just also $inc upsert at the beginning of each day to create a new document that transfers the balance from the previous day:

 // increase balance db.daily( { "dateEntry": currentDate }, { "$inc": { "balance": amount } }, { "upsert": true } ); // decrease balance db.daily( { "dateEntry": currentDate }, { "$inc": { "balance": -amount } }, { "upsert": true } ); // Each day var lastDay = db.daily.findOne({ "dateEntry": lastDate }); db.daily( { "dateEntry": currentDate }, { "$inc": { "balance": lastDay.balance } }, { "upsert": true } );