如何在MongoDB中执行有条件的算术操作

Question

我遵循模式，

{ 
    "_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
        }
    ]
}

付款方式为“费用”，我想获得所有付款总额

如果 给我语法错误，例如

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"}}
                }
            }
        }
     }
    
]);

，状况和切换语句，但两者都无法正常工作，或者我使用的是错误的。

Answer 1

您可以使用$ redion为此：

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"]}
            ]
          }
        }
      }
    }
  }
])

查看其在

Answer 2

您可以做简单的数学：

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