CODEWITHSUNDEEP
mongodbmapreduce
CoffeePeddlerIntern
CoffeePeddlerIntern

Reputation: 679

MongoDb Summing multiple columns

I'm dabbling in mongoDb and trying to use map reduce queries. I need to sum up multiple values from different columns (num1, num2, num3, num4, num5). Going off this guide http://docs.mongodb.org/manual/tutorial/map-reduce-examples/ . There I'm trying to alter the first example there to sum up all the values.

This is what I am trying/tried. I'm not sure if it can take in multiple values like this, I just assumed. 

var query1 = function(){ emit("sumValues", this.num1, this.num2, this.num3, this.num4, this.num5)};

    var query1query = function(sumValues, totalSumOfValues){ return Array.sum(totalSumOfValues); }

    db.testData.mapReduce( query1, query1query, {out: "query_one_results"})

This is the error I get. Sun Dec 1 18:52:24.627 JavaScript execution failed: map reduce failed:{ "errmsg" : "exception: JavaScript execution failed: Error: fast_emit takes 2 args near 'ction (){ emit(\"sumValues\", this.c' ", "code" : 16722, "ok" : 0

Is there another way to sum up all these values? Or where is my error in what I have.

I also tried this and it seemed to work. but when I do a .find() on the file it creates it only seems to be retrieving the values of sum5 and adding them together. 

var query1 = function(){ emit({sum1: this.sum1,sum2: this.sum2,sum3: this.sum3,sum4: this.sum4,sum5:  this.sum5},{count:1});}
 var query1query = function(key, val){ return Array.sum(val); };
 db.testData.mapReduce( query1, query1query, {out: "query_one_results"})
{
        "result" : "query_one_results",
        "timeMillis" : 19503,
        "counts" : {
                "input" : 173657,
                "emit" : 173657,
                "reduce" : 1467,
                "output" : 166859
        },
        "ok" : 1,
}

Ok I think I got it. This is what I ended up doing

> var map1= function(){
... emit("Total Sum", this.sum1);
... emit("Total Sum", this.sum2);
... emit("Total Sum", this.sum3);
... emit("Total Sum", this.sum4);
... emit("Total Sum", this.sum5);
... emit("Total Sum", this.sum6);
… };

> var reduce1 = function(key, val){
... return Array.sum(val)
... };
> db.delayData.mapReduce(map1,reduce1,{out:'query_one_result'});
{
        "result" : "query_one_result",
        "timeMillis" : 9264,
        "counts" : {
                "input" : 173657,
                "emit" : 1041942,
                "reduce" : 1737,
                "output" : 1
        },
        "ok" : 1,
}

> db.query_one_result.find()
{ "_id" : "Total Sum", "value" : 250 }

Upvotes: 0

Views: 1036

Answers (1)

Asya Kamsky
Asya Kamsky

Reputation: 42352

You were getting the two values that you need to emit in map backwards. The first one represents a unique key value over which you are aggregating something. The second one is the thing you want to aggregate.

Since you are trying to get a single sum for the entire collection (it seems) you need to output a single key and then for value you need to output the sum of the four fields of each document.

map = function() { emit(1, this.num1+this.num2+this.num3+this.num4+this.num5); }
reduce = function(key, values) { return Array.sum(values);   }

This will work as long as num1 through num5 are set in every document - you can make the function more robust by simply checking that each of those fields exists and if so, adding its value in.

In real life, you can do this faster and simpler with aggregation framework:

db.collection.aggregate({$group:{{$group:{_id:1, sum:{$sum:{$add:["$num1","$num2","$num3","$num4","$num5"]}}}})

Upvotes: 3

Related Questions