对集合中文档的相同命名字段的所有值求和 [英] 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)

谢谢！

推荐答案

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

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

蒙戈游乐场

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