Wednesday, 15 March 2017

How to aggregate data into quarterly buckets using MongoDB and Javascript?

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