Single aggregation query for multiple groups

Question

My Collection JSON

[
   {
      "_id" : 0,  
      "finalAmount":40,
      "payment":[
         {
            "_id":0,
            "cash":20
         },
         {
            "_id":1,
            "card":20
         }
      ]
   },
   {
      "_id" : 1,
      "finalAmount":80,
      "payment":[
         {
            "_id":0,
            "cash":60
         },
         {
            "_id":1,
            "card":20
         }
      ]
   },
   {
      "_id" : 2,
      "finalAmount":80,
      "payment":[
         {
            "_id":0,
            "cash":80
         }
      ]
   }
]

I want to have the amount, cash and card group wise using aggregation framework. Can anyone help?

Please consider my _id as an ObjectId for demo purpose as I have given 0 and 1. I am using Node Js and MongoDB and I want the expected output in just one query as follows:

Expected Output:

{
   "cash":160,
   "card":40,
   "total":200,
   "count":3
}

chridam · Accepted Answer · 2016-11-10 09:24:41Z

You could try running the following aggregation pipeline, although there might be some performance penalty or potential aggregation pipeline limits with huge datasets since your initial pipeline tries to group all the documents in the collection to get the total document count and the amount as well as pushing all the documents to a temporary list, which may affect performance down the pipeline.

Nonetheless, the following solution will yield the given desired output from the given sample:

collection.aggregate([
    {
        "$group": {
            "_id": null,
            "count": { "$sum": 1 },
            "doc": { "$push": "$$ROOT" },
            "total": { "$sum": "$finalAmount" }
        }
    },
    { "$unwind": "$doc" },
    { "$unwind": "$doc.payment" },
    {
        "$group": {
            "_id": null,
            "count": { "$first": "$count" },
            "total": { "$first": "$total" },            
            "cash": { "$sum": "$doc.payment.cash" },
            "card": { "$sum": "$doc.payment.card" }
        }
    }
], function(err, result) {
    console.log(result);
});

sergiuz · Accepted Answer · 2016-11-10 10:23:35Z

When running on big datasets, this problem might be more suitable, more fast to solve with a map reduce operation, since the result is one singel aggregated result.

var map = function map(){
    var cash = 0;
    var card = 0;

    for (i in this.payment){

        if(this.payment[i].hasOwnProperty('cash')){
            cash += this.payment[i]['cash']
        }
        if(this.payment[i].hasOwnProperty('card')){
            card += this.payment[i]['card']
        }
    }

    var doc = {
        'cash': cash,
        'card': card,
    };
    emit(null, doc);    
};

var reduce = function(key, values){

    var total_cash = 0;
    var total_card = 0;
    var total = 0;

    for (i in values){
        total_cash += values[i]['cash']
        total_card += values[i]['card']
    }

    var result = {
        'cash': total_cash,
        'card': total_card,
        'total': total_cash+ total_card,
        'count': values.length
    };

    return result
};


db.runCommand({"mapReduce":"test", map:map, reduce:reduce, out:{replace:"test2"}})

result:

db.test2.find().pretty()
    {
        "_id" : null,
        "value" : {
            "cash" : 160,
            "card" : 40,
            "total" : 200,
            "count" : 3
        }
    }

Collectives™ on Stack Overflow

Single aggregation query for multiple groups

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related