How to sum the value of a key across all documents in a MongoDB collection

Question

I have collection in MongoDB:

{ "_id" : ObjectId("4d2407265ff08824e3000001"), "subida" : 3.95 }
{ "_id" : ObjectId("4d2551b4ae9fa739640df821"), "subida" : 6.03 }
{ "_id" : ObjectId("4d255b115ff08821c2000001"), "subida" : 5.53 }
{ "_id" : ObjectId("4d25e8d55ff08814f8000001"), "subida" : 1.96 }

How I can sum the value of a key, e.g., "subida", across all documents? With the documents above, I should receive something along the lines of:

{ "subida" : 17.47 }

score 145 · Answer 1 · edited Jul 13 '15 at 02:20

145

In this case, aggregation is much simpler and much more efficient than mapReduce:

db.collection.aggregate({
    $group: {
        _id: '',
        subida: { $sum: '$subida' }
    }
 }, {
    $project: {
        _id: 0,
        subida: '$subida'
    }
})

use $group with $sum to calculate sum
use projection's $project operator to remove id key required by $group operator

edited Jul 13 '15 at 02:20

royhowie

11,075
14
50
67

answered Dec 14 '12 at 09:15

Ilya Builuk

2,189
2
16
10

2

This method is much MUCH more efficient than the current implementation of Map/Reduce – rdrkt Jan 20 '13 at 22:11
+1 excellent answer. I was going wrong with using {"$sum": {"subida":1 }} – Sagar Hatekar May 29 '13 at 14:41
1

Shamt that Spring DB does not support aggregation framework yet :( – will824 Jun 17 '13 at 23:42
@rdrkt - what's your testing/evidence of this solution being faster? – Kevin Meredith Oct 29 '13 at 00:34
4

MapReduce engine is a single-threaded javascript. It doesnt use any indexes either. The aggregation framework is multi-threaded, native c++ and uses indexes where possible. – rdrkt Dec 10 '13 at 10:31

score 18 · Accepted Answer · answered Jan 07 '11 at 01:46

I'd personally perform a mapreduce on the collection :

map is a simple function emitting the "subida" field. The key should the same if you need a single sum; the result after reduce will yield the single object {<key>: <sum>}, with <key> being whatever value you supplied in the emit.

map = function() { emit(<key>, this.subida); }

reduce is also a simple function summing them :

red = function(k, v) {
  var i, sum = 0;
  for (i in v) {
    sum += v[i];
  }
  return sum;
}

You can then call mapreduce on your collection <mycollection>:

res = db.<mycollection>.mapReduce(map, red);

Which will create a temporary new collection you can manipulate like any other collection. The value returned by mapReduce holds several values regarding the mapReduce such as the time taken, status..., as well as the temp. collection name created in the "result" field. To get the values you need, you have to query that collection :

db[res.result].find()

Which should give you the object {<key>: <sum>}.

If you run MongoDB 1.7.4 or higher, you can save you a bit of hassle by asking MongoDB to return the result directly without creating a collection :

db.<mycollection>.mapReduce(map, red, {out : {inline: 1}});

Thank you very much friend, I can finally make this query, works perfect in mongo shell. I was implemented in python in my application... I see now that mongodb map/reduce queries are not equal to CouchDB map/reduce queries... LOL :D — JAM, Jan 07 '11 at 05:50
Just interested why you would choose mapreduce over aggregation? — UpTheCreek, Nov 14 '17 at 15:29

score 9 · Answer 3 · answered May 21 '19 at 05:22

9

Use this simplest query to get the sum of the result

db.collection.aggregate({
    $group: {
        _id: '',
        subida: { $sum: '$subida' }
    }
 }
)

answered May 21 '19 at 05:22

Sathiamoorthy

8,831
9
65
77

doesnj't work with mongoose `server error MongooseError: Mongoose 5.x disallows passing a spread of operators to `Model.aggregate()`. Instead of `Model.aggregate({ $match }, { $skip })`, do `Model.aggregate([{ $match }, { $skip }])`` – chovy Dec 26 '20 at 01:21

Ian Mercer · Answer 4 · 2017-12-12T14:58:02.970

Option 0: Use the MongoDB aggregation pipeline
[NB: this option was added a long time after this question was asked but is the right approach now]

Option 1: Query all the records, return only the subida field from Mongo and add them up by iterating over the Mongo cursor client side.

Option 2: Write a map reduce command that emits only the subdia field (same key for all) and then a reduce command that totals them.

Option 3: Use db.eval to execute javascript on the server: http://www.mongodb.org/display/DOCS/Server-side+Code+Execution

Option 4: Accumulate 'subida' values as you insert values into your collection so that you have an up-to-date total at hand whenever you need it. You can store the total in a different document and use atomic "update if current" operations to update it: http://www.mongodb.org/display/DOCS/Atomic+Operations

Any example will be more alive and easy understand? – Eddy May 06 '16 at 09:13 — Eddy, May 06 '16 at 09:13

oscarcardoso · Answer 5 · 2018-01-14T22:04:50.547

0

You are able to use the collection as an array, just add the values in a loop.

Edit: Yes, this is a bad practice when having a big dataset.

edited Jan 14 '18 at 22:04

answered Jan 07 '11 at 00:20

oscarcardoso

168
2
10

9

bad practice on large datesets – sinoohe Jun 24 '15 at 06:08

How to sum the value of a key across all documents in a MongoDB collection

5 Answers5

Linked