Muhammad Ateek Muhammad Ateek - 4 months ago 7
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
totalAmount
of matching objects. So for this purpose I'm using this query:

Transaction.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_id
along with
totalAMount
and other fields like
description
. It should be some thing like this

[{
"_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"
}
Comments