Actually more suited to mapReduce than the aggregation framework, at least in the initial problem solving. The aggregation framework has no concept of the value of a previous document, or the previous "grouped" value of a document so this is why it cannot do this.
On the other hand, mapReduce has a "global scope" that can be shared between stages and documents as they are processed. This will get you the "running total" for the current balance at end of day you require.
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 }
}
)
That will sum by date grouping and then in the "finalize" section it makes a cumulative sum 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 longer term you would be best of having a separate collection with an entry for each day an alter the balance using $inc
in an update. Just also do an $inc
upsert at the beginning of each day to create a new document carrying forward 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 }
);
How NOT to do this
Whilst it is true that since the original writing there are more operators introduced to the aggregation framework, what is being asked here is still not practical to do in an aggregation statement.
The same basic rule applies that the aggregation framework cannot reference a value from a previous "document", nor can it store a "global variable". "Hacking" this by coercion of all results into an array:
db.collection.aggregate([
{ "$group": {
"_id": {
"y": { "$year": "$dateEntry" },
"m": { "$month": "$dateEntry" },
"d": { "$dayOfMonth": "$dateEntry" }
},
"amount": { "$sum": "$amount" }
}},
{ "$sort": { "_id": 1 } },
{ "$group": {
"_id": null,
"docs": { "$push": "$$ROOT" }
}},
{ "$addFields": {
"docs": {
"$map": {
"input": { "$range": [ 0, { "$size": "$docs" } ] },
"in": {
"$mergeObjects": [
{ "$arrayElemAt": [ "$docs", "$$this" ] },
{ "amount": {
"$sum": {
"$slice": [ "$docs.amount", 0, { "$add": [ "$$this", 1 ] } ]
}
}}
]
}
}
}
}},
{ "$unwind": "$docs" },
{ "$replaceRoot": { "newRoot": "$docs" } }
])
That is neither a performant solution or "safe" considering that larger result sets run the very real probability of breaching the 16MB BSON limit. As a "golden rule", anything that proposes to put ALL content within the array of a single document:
{ "$group": {
"_id": null,
"docs": { "$push": "$$ROOT" }
}}
then that is a basic flaw and therefore not a solution.
Conclusion
The far more conclusive ways to handle this typically would be post processing on the running cursor of results:
var globalAmount = 0;
db.collection.aggregate([
{ $group: {
"_id": {
y: { $year:"$dateEntry"},
m: { $month:"$dateEntry"},
d: { $dayOfMonth:"$dateEntry"}
},
amount: { "$sum": "$amount" }
}},
{ "$sort": { "_id": 1 } }
]).map(doc => {
globalAmount += doc.amount;
return Object.assign(doc, { amount: globalAmount });
})
So in general it's always better to:
Use cursor iteration and a tracking variable for totals. The mapReduce
sample is a contrived example of the simplified process above.
Use pre-aggregated totals. Possibly in concert with cursor iteration depending on your pre-aggregation process, whether that is just interval total or a "carried forward" running total.
The aggregation framework should really be used for "aggregating" and nothing more. Forcing coercions on data via processes like manipulating into an array just to process how you want is neither wise or safe, and most importantly the client manipulation code is far cleaner and more efficient.
Let databases do the things they are good at, as you "manipulations" are far better handled in code instead.