Muhammad Ateek Muhammad Ateek - 4 months ago 8
Node.js Question

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.

Answer

You should use aggregate and $group for this purpose

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