compare sum due date with current date by condional in aggregate mongodb?

Question

I have some problem with aggregate, I have one collection.I have collection like code below. so on I have one field call dueDate compare with current Date 2018-11-16. I need sum remaining value by compare that current date with dueDate totalfirstAmount from remaining compare no duDate and dueDate less than or equal curren date and another second totalSecondAmount from remaining find only dueDate greater current date

let myCollection=
  {
  "_id" : "001-29", 
  "tranType" : "Bill",
  "tranDate" : ISODate("2018-11-16T14:55:16.621+07:00"),    
  "vendorId" : "001-2", 
  "dueDate" : ISODate("2018-11-17T14:55:16.621+07:00"), 
  "remaining" : 45,
  "branchId" : "001",     
  },

/* 2 */
  {
  "_id" : "001-26", 
  "tranType" : "Bill",
  "tranDate" : ISODate("2018-11-15T14:22:48.138+07:00"),    
  "vendorId" : "001-9", 
  "withdrawal" : 0,
  "remaining" : 90,
  "branchId" : "001",     
  "memo" : null,    
  },

/* 3 */
  {
  "_id" : "001-18", 
  "tranType" : "Bill",
  "tranDate" : ISODate("2018-11-08T14:18:36.543+07:00"),    
  "vendorId" : "001-1", 
  "billDate" : ISODate("2018-11-15T14:18:36.543+07:00"),
  "dueDate" : ISODate("2018-11-15T14:18:36.543+07:00"), 
  "remaining" : 450,      
  "memo" : null
  },

I want result like below

"lowerDueTotalRemaining" : 540,
"overDueTotalRemaining" : 45

Answer 1

We can use aggregation pipeline to get the desired result.

Pipeline stages $project and $group are used together with pipeline operators $sum and $cond

In the collection given, a condition has to be executed to find whether the given document is overdue or lowerdue, the first part of the below query does the segregation of documents into overdue and lower due. For our grouping convenience at the next stage I have added a flag "O" for OverDue and "L" for LowerDue.

db.collection_name.aggregate([
  {
    $project: {
      summation: {
        $cond: {
          if: {
            $gte: ["$dueDate", new Date("2018-11-16")]
          },
          then: {
            overDueTotalRemaining: { $sum: "$remaining" },
            flag: "O"
          },
          else: {
            lowerDueTotalRemaining: { $sum: "$remaining" },
            flag: "L"
          }
        }
      }
    }
  },
  {
    $group: {
      _id: "$summation.flag",
      lowerDueTotalRemaining: {
        $sum: "$summation.lowerDueTotalRemaining"
      },
      overDueTotalRemaining: {
        $sum: "$summation.overDueTotalRemaining"
      }
    }
  },
  {
    $project: {
      ans: {
        $cond: {
          if: {
            $eq: [ "$_id", "O" ]
          },
          then: {
            "overDueTotalRemaining": "$overDueTotalRemaining"
          },
          else: {
            "lowerDueTotalRemaining": "$lowerDueTotalRemaining"
          }
        }
      }
    }
  }
]);

Matching answer with some extra attributes which we get while executing the above query is shown below

{ "_id" : "L", "ans" : { "lowerDueTotalRemaining" : 540 } }
{ "_id" : "O", "ans" : { "overDueTotalRemaining" : 45 } }