将集合中文档的相同命名字段的所有值相加 [英] Sum all values of same named fields of docs in a collection

问题描述

我有一个包含以下文档的数据库:

I have a database with the following documents:

{ name: "John", a: 20, b: 30, c: 40, d: 50  },     
{ name: "Rich", a: 20, b: 30, c: 40, d: 50  },      
{ name: "Anne", a: 20, b: 30, c: 40, d: 50  },      
{ name: "Sam", a: 20, b: 30, c: 40, d: 50  },

我想计算在每个字段中花费的总小时数.我通过以下方式实现了这一点:

I want to calculate the total of hours spent in each of these fields. I accomplished that by:

db.hours.aggregate([{$group: {_id: null, totalA: {$sum: "$a"}, totalB: {$sum: "$b"}, totalC: {$sum: "$c"}, totalD: {$sum: "$d"}}}])
{ "_id" : null, "totalA" : 80, "totalB" : 120, "totalC" : 160, "totalD" : 200 }

由于在某个时候每个文档中都会有几十个字段，有没有更简单的方法可以动态生成总字段?(如:检查文档中的所有字段，如果它们存在于集合中的其他文档中，则将它们全部相加.如果它们不存在于其他文档中，则将该字段值显示为总计).例如，如果我有:

Since at some point there will be dozens of fields in each document, is there any easier way to have the total fields be generated dynamically? (as in: check all fields in a document and if they exist in other docs in the collection, sum them all. If they don't exist in other docs, just display that field value as the total). For example, if I have:

{ name: "John", a: 20, b: 30, e: 40, f: 50  },     
{ name: "Rich", a: 20, b: 30, c: 40, d: 50  },      
{ name: "Anne", a: 20, b: 30, g: 40, h: 50  },      
{ name: "Sam", a: 20, b: 30, c: 40, d: 50  },

应该导致:

{"a" : 80, "b" : 120, "c" : 80, "d" : 100, "e" : 40, "f" : 50, "g" : 40, "h": 50 }

有什么建议吗?(无需像上面的聚合示例那样手动写入所有总和)

Any suggestions? (without manually writing all the sums as in the aggregate example above)

谢谢！

推荐答案

您可以利用 $objectToArray 和 $arrayToObject 运算符来动态读取您的对象键.要摆脱 _id 和 name 字段，您可以 $filter by $类型.

You can take advantage of $objectToArray and $arrayToObject operators to dynamically read your object keys. To get rid of _id and name fields you can $filter by $type.

db.collection.aggregate([
    {
        $project: {
            _id: 0,
            fields: {
                $filter: {
                    input: { $objectToArray: "$$ROOT" },
                    cond: { $eq: [ { $type: "$$this.v" }, "double" ] }
                }
            }
        }
    },
    {
        $unwind: "$fields"
    },
    {
        $group: {
            _id: "$fields.k",
            total: { $sum: "$fields.v" }
        }
    },
    {
        $group: {
            _id: null,
            aggregates: { $push: { k: "$_id", v: "$total" } }
        }
    },
    {
        $replaceRoot: {
            newRoot: { $arrayToObject: "$aggregates" }
        }
    }
])

Mongo 游乐场

这篇关于将集合中文档的相同命名字段的所有值相加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！