1

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!:)

0 Answers0