mongodb - 如何从 mongo 聚合中获得想要的结果

Question

在 mongo 项目集合中有这样的数据：

> db.items.find()
{ "_id" : ObjectId("5e9d57f8b018eecfd91c785a"), "customer_id" : 1, "goods_id" : 10, "op_code" : 1, "op_value" : 5 }
{ "_id" : ObjectId("5e9d57f8b018eecfd91c785b"), "customer_id" : 1, "goods_id" : 10, "op_code" : 1, "op_value" : 2 }
{ "_id" : ObjectId("5e9d57f8b018eecfd91c785c"), "customer_id" : 1, "goods_id" : 10, "op_code" : 2, "op_value" : 5 }

“op_code”字段代表加减运算（加运算值为1，减运算值为2），“op_value”字段为绝对值。如何按“customer_id”和“goods_id”字段计算“op_value”组的总价值。

想要的结果如下：

# "total" field stands for sum of "op_value", "op_code" value 1 is plus while 2 is minus.
{ "_id" : { "customer_id" : 1, "goods_id" : 10}, "total" : 2 } # 5 + 2 - 5

mongo 版本是：3.2.12

谢谢。

标签： mongodbaggregation

Answer 1

这正在按您的预期工作，

  db.items.aggregate(
    {
        $group: {
            _id: {
                "customer_id": "$customer_id",
                "goods_id": "$goods_id"
            },
            "pos": { 
               "$sum": { 
                   "$cond": [
                       { "$eq": [ "$op_code", 1 ] },
                       "$op_value",
                       0
                   ]
               }
            },
            "neg": { 
               "$sum": { 
                   "$cond": [
                       { "$eq": [ "$op_code", 2 ] },
                       "$op_value",
                       0
                   ]
               }
            }
        }
    },
    { "$project": {
       "_id": "$_id",
       "total": { "$subtract": [ "$pos", "$neg" ] }
    }})