CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Alfredo Alvarez
Alfredo Alvarez

Reputation: 334

Sum operation on documents with different structures

I'm trying to use the aggregation pipeline to be able to do a report table with (sort,paging, etc) using mongodb for my back end. I run into a problem that my schema is not the same but i need to add all the values. I was trying to use the unwind command but it only works in arrays so run out of alternatives.

Sample Documents

{
  "_id": "first",
  "apple": 6,
  "pears": 7,
  "total_fruits": "13"
},

{
  "_id": "second",
  "apple": 6,
  "bananas": 2,
  "total_fruits": "8"
}

Desired Result

{
  "_id": "result",
  "apple": 12,
  "pears": 7,
  "bananas": 2,
  "total_fruits": "21"
}

Upvotes: 2

Views: 57

Answers (1)

Nick Bull
Nick Bull

Reputation: 9866

Instead of aggregating, why not use mapReduce()?

function sumKeyFromCollection(collectionName, keyName) {
    map = function() { emit(keyName, this[keyName]); }
    red = function(k, v) {
      var i, sum = 0;
      for (i in v) {
        sum += v[i];
      }
      return sum;
    }
    return db[collectionName].mapReduce(map, red, {out : {inline: 1}});
}

For your example documents:

{
  "_id": "first",
  "apple": 6,
  "pears": 7,
  "total_fruits": "13"
},
{
  "_id": "second",
  "apple": 6,
  "bananas": 2,
  "total_fruits": "8"
}

You could call the function on each field name:

console.log(sumKeyFromCollection("collectionName", "apple"));
> { "_id": "...", "apple": 12 }

Then use each of them to produce your resultant document:

var fruits = [ "apple", "bananas", "total_fruits" ];
var results = []; 

for (var i = 0; i < fruits.length; i++) {
  results.push(sumKeyFromCollection("collectionName", fruits[i]));
}

EDIT Also, to get every field name in a collection programmatically:

function getAllCollectionFieldNames(collectionName) {
    mr = db.runCommand({
      "mapreduce" : collectionName,
      "map" : function() {
        for (var key in this) { emit(key, null); }
      },
      "reduce" : function(key, stuff) { return null; }, 
      "out": "my_collection" + "_keys"
    });
    db[mr.result].distinct("_id");
}

Which returns an array of unique field names (even if they only appear in one document).

Upvotes: 1

Related Questions