Question 117368 in Mongodb Question posted in
The official documentation can be found here.

How to perform conditional arithmetic operations in MongoDB

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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" } } }
])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search