Node.js Question
How to get other fields of objects when using aggregate and groups
I have a transaction model in which I have description and status fields of transactions. Like the following
[
{
"_id": "5795e3f4f4a0fb8c1dff20ad",
"description": "This is a transcation",
"invoice_id": "5795db00bfa9d366194a454d",
"amount": 50
},
{
"_id": "5795e3faf4a0fb8c1dff20ae",
"description": "This is a transcation",
"invoice_id": "5795db00bfa9d366194a454d",
"amount": 100
}
]
I'm using aggregation and grouping these records to find
totalAmountTransaction.aggregate([{
$match: {
invoice: ObjectId("5795db00bfa9d366194a454d")
}
}, {
$group: {
_id: {
_id: "$_id"
},
count: {
$sum: 1
},
totalAmount: {
$sum: "$amount"
},
}
}
])
When I run this, I get the following result
/* 1 */
{
"_id": {
"_id": ObjectId("5796031e07bad1d21f3af823")
},
"count": 1.0,
"totalAmount": 100
}
/* 2 */
{
"_id": {
"_id": ObjectId("5795e3f4f4a0fb8c1dff20ad")
},
"count": 1.0,
"totalAmount": 50
}
but I want to get all those matching objects which have specific
invoice_idtotalAMountdescription[{
"_id": {
"_id": ObjectId("5795e3f4f4a0fb8c1dff20ad")
},
"description": "description of first object",
"count": 5.0,
"totalAmount": 350
}, {
"_id": {
"_id": ObjectId("5795e3f4f4a0fb8c1dff20ad")
},
"description": "description of second object",
"count": 5.0,
"totalAmount": 350
}]
How do I achieve this solution?
Answer
Add into $group expression description: { $first: "$description" }. Like
Transaction.aggregate([{
$match: {
invoice: ObjectId("5795db00bfa9d366194a454d")
}
}, {
$group: {
_id: {
_id: "$_id"
},
count: {
$sum: 1
},
totalAmount: {
$sum: "$amount"
},
// here it is
description: {
$first: "$description"
}
}
}
])
Update
For those who have problems with the query above (grouping by _id which is unique is non-sense operation) here is more logic-friendly code:
db.invoices.aggregate([{
$match: {
"invoice_id": ObjectId("5795db00bfa9d366194a454d")
}
}, {
$group: {
_id: {
_id: "$invoice_id"
},
count: {
$sum: 1
},
totalAmount: {
$sum: "$amount"
},
description: {
$first: "$description"
}
}
}])
Update 2
Strange query but let's have fun
db.invoices.aggregate([{
$match: {
"invoice_id": ObjectId("5795db00bfa9d366194a454d")
}
}, {
$group: {
_id: {
_id: "$invoice_id"
},
count: {
$sum: 1
},
info: {
$push: {
id: "$_id",
amount: "$amount",
description: "$description"
}
},
totalAmount: {
$sum: "$amount"
}
}
}, {
$unwind: "$info"
}, {
$project: {
_id: "$info.id",
count: "$count",
invoice_id: "$_id",
totalAmount: "$totalAmount",
amount: "$info.amount",
description: "$info.description"
}
}])
Now you will be able to get results in this form
/* 1 */
{
"_id" : ObjectId("5795e3f4f4a0fb8c1dff20ad"),
"count" : 2.0,
"totalAmount" : 150,
"invoice_id" : {
"_id" : ObjectId("5795db00bfa9d366194a454d")
},
"amount" : 50,
"description" : "This is a transcation"
}
/* 2 */
{
"_id" : ObjectId("5796031e07bad1d21f3af823"),
"count" : 2.0,
"totalAmount" : 150,
"invoice_id" : {
"_id" : ObjectId("5795db00bfa9d366194a454d")
},
"amount" : 100,
"description" : "FSC evening class"
}
Source (Stackoverflow)