CODEWITHSUNDEEP
node.jsmongodbmongoose
Muhammad Ateek
Muhammad Ateek

Reputation: 1057

how to get sum of specific numeric field with all matched records

im using mongoose with node.js and i have transactions model and invoice model. i wants to get all those records which have specific invoice id. im using this query to get matched transactions of a invoice.

Transaction.find({invoice_id: invoiceId})

im getting records in this form

[
  {
    "_id": "5795e3f4f4a0fb8c1dff20ad",
    "description": "This is a transcation",
    "invoice_id": "5795db00bfa9d366194a454d",
    "amount": 50
  },
  {
    "_id": "5795e3faf4a0fb8c1dff20ae",
    "description": "This is a transcation",
    "invoice_id": "5795db00bfa9d366194a454d",
    "amount": 100
  }
]

but the problem is that i also wants to get totalAmount by sum "amount" fields values in each object of transaction array. my desired result is that

[
  {
    "_id": "5795e3f4f4a0fb8c1dff20ad",
    "description": "This is a transcation",
    "invoice_id": "5795db00bfa9d366194a454d",
    "amount": 50
  },
  {
    "_id": "5795e3faf4a0fb8c1dff20ae",
    "description": "This is a transcation",
    "invoice_id": "5795db00bfa9d366194a454d",
    "amount": 100
  },
{
totalAmount: 150
}
]

im using $sum of aggregation functions but i dont know how my problem will solved with this.

Upvotes: 1

Views: 150

Answers (2)

Azeem Malik
Azeem Malik

Reputation: 490

You should use aggregate and $group for this purpose

  Transaction.aggregate([{
                $group: {
                   _id:{ invoice_id:"$invoice_id"
                    },
                    count: {
                        $sum: 1
                    },
                  totalAmount: { $sum: "$amount" },
                }
            }]).exec()

Upvotes: 1

Odonno
Odonno

Reputation: 419

In this way, you should preferably use the mapReduce function of mongodb. Here is an example :

var map = function() { emit(this.invoice_id, this.amount); };

var reduce = function(key, values) {
    var totalAmount = 0;
    for (var i + 0; i < values.length; i++) { totalAmount += values[i]; }
    return totalAmount;
};

var res = db.mapReduce(map, reduce);

If you really want a single invoice id, here is the updated map function to use :

var id = "1";
var map = function() { emit(id, this.amount); };

Upvotes: 0

Related Questions