Sum all values of same named fields of docs in a collection

Question

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  },

Should lead to:

{"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)

Thanks!

score 3 · Accepted Answer · answered Nov 20 '19 at 13:22

3

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 Playground

answered Nov 20 '19 at 13:22

mickl

48,568
9
60
89

Is there any way to have the results sorted? In Mongo playground I get the desired result [ { "a": 80, "b": 120, "c": 80, "d": 100, "e": 40, "f": 50, "g": 40, "h": 50 } ] but in my console it shows up unsorted { "h" : 50, "c" : 80, "g" : 40, "f" : 50, "e" : 40, "d" : 100, "b" : 120, "a" : 80 }. Tried adding a {$sort: {_id: 1}} or something like that, to no avail, don't know the name of the variables after the $replaceRoot phase (interested in sorted alphabetically or by total amount) – jlo Nov 22 '19 at 10:39
1

@jio I understand that it looks bad however you shouldn't rely on the order of BSON keys - it's meant to be non-deterministic: https://stackoverflow.com/questions/20701412/order-of-key-values-in-mongodb , maybe you can try to put your $sort between $group stages like: https://mongoplayground.net/p/PB0Fq7-GlHW – mickl Nov 22 '19 at 20:06

Sum all values of same named fields of docs in a collection

1 Answers1

Linked