I've following schema

{ 
    "_id" : ObjectId("xxxxx"), 
    "updatedAt" : ISODate("2022-06-29T13:10:36.659 0000"), 
    "createdAt" : ISODate("2022-06-29T08:06:51.264 0000"), 
    "payments" : [
        {
            "paymentId" : "xxxxx", 
            "paymentType" : "charge", 
            "paymentCurrency" : "PKR", 
            "paymentMode" : "cash", 
            "paymentTotal" : 13501.88, 
            "penalties" : 100
        }, 
        {
            "paymentId" : "ccccc", 
            "paymentType" : "refund", 
            "paymentCurrency" : "PKR", 
            "paymentMode" : "", 
            "paymentTotal" : 13061.879999999997, 
            "penalties" : 430.0
        }
    ]
}

I want to get all paymentTotal sum if paymentType is 'charge' else subtract the paymentTotal from the sum if paymentType is other than charge, i.e refund also subtract penalties from total sum

I've tried following query which is not working giving me syntax error like,

A syntax error was detected at the runtime. Please consider using a higher shell version or use the syntax supported by your current shell.

xxx

Blockquote

db.getCollection("booking").aggregate([
    {
        $match: {
            createdAt : {
            "$gte":ISODate("2022-06-28"),
            "$lte":ISODate("2022-06-30"),
        }
      }
    },
    {$unwind: '$payments'}, 
    {
        "$group":{
            "_id" : "$_id",
            "total" : {
                     $sum: "$payments.paymentTotal"
                }
            },
     },
     {
        $project :
        {
            "grandTotal":{
                $cond:{
                    if:{$eq:["$payments.paymentType", "charge"]},
                    then:{$add : {"$total,$payments.paymentTotal"}},
                    else:{ $subtract: {"$total,$payments.paymentTotal"}}
                }
            }
        }
     }
    
]);

I've tried, Condition and Switch statements but both are not working, or maybe I'm using them wrong.

CodePudding user response：

You can use $reduce for it:

db.collection.aggregate([
  {
    $match: {
      createdAt: {
        $gte: ISODate("2022-06-28T00:00:00.000Z"),
        $lte: ISODate("2022-06-30T00:00:00.000Z")
      }
    }
  },
  {
    $project: {
      grandTotal: {
        $reduce: {
          input: "$payments",
          initialValue: 0,
          in: {
            $cond: [
              {$eq: ["$$this.paymentType", "charge"]},
              {$add: ["$$this.paymentTotal", "$$value"]},
              {$subtract: ["$$value", "$$this.paymentTotal"]}
            ]
          }
        }
      }
    }
  }
])

See how it works on the playground example

CodePudding user response：

You can do simple math:

db.collection.aggregate([
   {
      $set: {
         grandTotal: {
            $map: {
               input: "$payments",
               in: {
                  $multiply: [
                     "$$this.paymentTotal",
                     { $cond: [{ $eq: ["$$this.paymentType", "charge"] }, 1, -1] }
                  ]
               }
            }
         }
      }
   },
   { $set: { grandTotal: { $sum: "$grandTotal" } } }
])