时间戳

首页 > 解决方案 > How to add every other columns together in Mongo?

mongodb - How to add every other columns together in Mongo?

问题描述

I've been cracking my head over the addition of every 'other' columns together during aggregation in Mongo.

A sample of my data:

[
{'item': 'X',
'USA': 3,
'CAN': 1,
'CHN': 1,
'IDN': 1,
   :
   :
   :
},
{'item': 'R',
'USA': 2,
'CAN': 2,
'CHN': 1,
'IDN': 2,
   :
   :
   :
}
]

At the aggregate stage, I would like to have a new field called 'OTHER', which is the resultant of the summation of all the fields that are not specified.

My desired result is this:

[
{'item': 'X',
'NAM': 79,
'IDN': 51,
'OTHER': 32
},
{'item': 'R',
'NAM': 42,
'IDN': 11,
'OTHER': 20
}
]

So far, the closest I could get is using this:

mycoll.aggregate([
{'$addFields':{
            'NAM': {'$add':[{'$ifNull':['$CAN', 0]},{'$ifNull':['$USA', 0]}]},
            'INDIA': {'$ifNull':['$IDN', 0]},
            'OTHER': /* $add all the fields that are not $USA, $CAN, $IDN*/
}},
])

Mongo gurus, please enlighten this poor soul. Deeply appreciate it. Thanks!

标签： mongodbnosqlaggregation-frameworknosql-aggregation

解决方案

一般来说，这个想法是将您的文档转换为一个数组，以便我们可以在忽略不需要的字段的同时对其进行迭代。

 {
     '$addFields': {
         'NAM': {'$add': [{'$ifNull': ['$CAN', 0]}, {'$ifNull': ['$USA', 0]}]},
         'INDIA': {'$ifNull': ['$IDN', 0]},
         "OTHER": {
             $reduce:
                 {
                     input: {"$objectToArray": "$$ROOT"},
                     initialValue: {sum: 0},
                     in: {
                         sum: {
                             $cond: {
                                 if: {$in: ["$$this.k", ['_id', "item", "CAN", "USA", "IDN"]]},
                                 then: "$$value.sum",
                                 else: {$add: ["$$value.sum", "$$this.v"]}
                             }
                         }
                     }
                 }
         }

     }
 }

显然，您还应该在文档中添加您不想总结/不是数字类型的任何其他字段。

推荐阅读