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.
Sample document where,
cashFlowField = "Balance"; cashFlowDateField = "CreateDate";
{
"Id" : "2",
"DocNumber" : "1002",
"CreatedDate" : ISODate("2016-04-07T00:00:00.000Z"),
"CustomerRef" : {
"value" : "22",
"name" : "EdgeLink, LLC"
},
"DueDate" : ISODate("2016-04-01T00:00:00.000Z"),
"TotalAmt" : 1950,
"Balance" : 1950,
"MetaData" : {
"CreateTime" : ISODate("2016-03-10T00:45:50.000Z"),
"LastUpdatedTime" : ISODate("2016-03-10T00:45:50.000Z")
}
},
Edit: I have also tried in vain in implementing the following code using $in operator. I'm not sure what's going wrong in here.
db.collection.aggregate([
{$project:{"date":1,
"quarter":{$cond:[{$in:[{$month:"$CreatedDate"},[4,5,6]},
"first",
{$cond:[{$in:[{$month:"$CreatedDate"},[7,8,9]},
"second",
{$in:[{$lte:[{$month:"$CreatedDate"}, [10,11,0]},
"third",
"fourth"]}]}]}}},
{$group:{"_id":{"quarter":"$quarter"},"results":{$push:"$date"}}}
])
Thanks in advance for your help!:)