MongoDB聚合中如何实现支付字段的条件算术运算

现有文档结构 #

集合中单条booking文档结构如下：

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

统计需求 #

总金额计算规则：

• 若paymentType为charge，累加对应paymentTotal到总和
• 若paymentType为其他类型（如refund），从总和中扣除对应paymentTotal，同时扣除对应penalties值

原代码问题 #

原聚合查询运行时抛出语法错误，提示如下：

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

原错误代码：

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

错误原因 #

1. 阶段逻辑错误：$unwind拆分数组后直接按_id分组求和，分组后输出的文档仅保留_id和total字段，后续$project阶段引用$payments.paymentType属于不存在的字段，无法完成类型判断
2. 运算符语法错误：$add、$subtract这类算术聚合运算符要求传入数组格式的运算参数列表，原代码将两个字段名拼成字符串作为对象属性传入，完全不符合MongoDB聚合语法规范
3. 计算逻辑错误：原代码先无差别累加所有paymentTotal，后续再判断类型做加减，根本无法实现非charge类型同时扣减penalties的需求

正确实现代码 #

不需要额外拆分$project阶段做判断，直接在$group求和阶段结合$cond计算单条支付记录的贡献值再统一累加即可，代码如下：

db.getCollection("booking").aggregate([
    {
        $match: {
            createdAt: {
                $gte: ISODate("2022-06-28"),
                $lte: ISODate("2022-06-30")
            }
        }
    },
    { $unwind: "$payments" },
    {
        $group: {
            _id: "$_id",
            grandTotal: {
                $sum: {
                    $cond: [
                        { $eq: ["$payments.paymentType", "charge"] },
                        // charge类型直接累加paymentTotal
                        "$payments.paymentTotal",
                        // 其他类型：累加 负的(paymentTotal+penalties)，实现双重扣减
                        { $multiply: [ -1, { $add: ["$payments.paymentTotal", "$payments.penalties"] }] }
                    ]
                }
            }
        }
    }
])

代码说明 #

• 保留原$match阶段的时间范围过滤逻辑
• $unwind拆分数组后不做无差别求和，在$group的$sum逻辑中直接通过$cond判断每条支付记录的计算值
• 非charge类型的记录，先计算paymentTotal + penalties的待扣减总和，乘以-1转为负数，$sum累加时自动完成扣减逻辑
• 去掉冗余的$project阶段，分组时直接输出最终grandTotal字段，减少聚合阶段数提升查询性能

内容的提问来源于stack exchange，提问作者Waqas Hafeez