Could you guys please help me with the following query? I'm trying to modify the following piece of code for an application that I am working on.
MongoClient.connect(url, function(err, db) {
assert.equal(null, err);
db.collection(collection).aggregate(
{
$match : {Id: uniqueKey}
},
{ $unwind : '$' + docToUnwind },
{
$project: {
_id: 1,
groupA: ('$' + docToUnwind + '.' + cashFlowField),
groupB: ('$' + docToUnwind + '.' + cashFlowDateField),
groupC: ('$Invoices.TotalAmt')
}
},
{
$match : {
groupB: { $lte: new Date(endDate),
$gte: new Date(beginDate) }
}
},
{
$project: {
groupA: 1,
groupC: 1,
buckets: {
"yr" : {"$year" : "$groupB"},
"mo" : {"$month" : "$groupB"}
},
}
},
{
$group: {
_id: "$buckets",
balance: {$sum:'$groupA'},
total: {$sum:'$groupC'}
}
},
function(err, result) {
if (result) {
callback(result);
} else {
callback(false);
}
}
);
});
Output:
[ { _id: { yr: 2016, mo: 4 }, balance: 10, total: 10 },
{ _id: { yr: 2016, mo: 11 }, balance: 20, total: 20 },
{ _id: { yr: 2016, mo: 9 }, balance: 30, total: 30 },
{ _id: { yr: 2016, mo: 7 }, balance: 40, total: 40 },
{ _id: { yr: 2016, mo: 5 }, balance: 50, total: 50 },
{ _id: { yr: 2016, mo: 6 }, balance: 60, total: 60 },
{ _id: { yr: 2017, mo: 2 }, balance: 70, total: 70 } ]
Is there a way that I can modify the above code to aggregate the above results into quarterly(or to be more generic - any frequency) buckets? The expected output that I am looking for is:
[ { _id: { qtr: Q1}, balance: 120, total: 120 },
{ _id: { qtr: Q2}, balance: 70, total: 70 },
{ _id: { qtr: Q3}, balance: 20, total: 20 },
{ _id: { qtr: Q4}, balance: 70, total: 70 }]
Additional Info:
yr: 2016, mo: 4, 5, 6 would fall into first bucket.
yr: 2016, mo: 7, 8, 9 would fall into second bucket.
yr: 2016, mo: 10, 11 and yr: 2017 mo: 0 would fall into third bucket.
yr: 2017, mo: 1, 2, 3 would fall into fourth bucket.
Thanks in advance for your help!:)
via Ashwin Kodialbail
No comments:
Post a Comment