I'm doing some aggregation to get some statistics on a how many products there are in cateories.
After some piping I'm down to this:
[
{
topCategory: "Computer",
mainCategory: "Stationary"
},
{
topCategory: "Computer",
mainCategory: "Laptop"
},
{
topCategory: "Computer",
mainCategory: "Stationary"
},
{
topCategory: "Computer",
mainCategory: "Stationary"
},
]
Wanted output:
[
{
name: "Computer",
count: 4,
mainCategories: [
{
name: "Laptop",
count: 2
},
{
name: "Stationary",
count: 2
}
]
}
]
My query so far:
let query = mongoose.model('Product').aggregate([
{
$match: {
project: mongoose.Types.ObjectId(req.params.projectId)
}
},
{ $project: {
_id: 0,
topCategory: '$category.top',
mainCategory: '$category.main',
}
},
]);
I know I need to use $group combined with $sum. I tried different way's but can't get it work.
In this case first you should group by mainCategory and then group by topCategory as below
db.collection.aggregate({
$group: {
_id: "$mainCategory",
"count": {
$sum: 1
},
"data": {
"$push": "$$ROOT"
}
}
}, {
"$unwind": "$data"
}, {
"$group": {
"_id": "$data.topCategory",
"count": {
"$sum": 1
},
"mainCategories": {
"$addToSet": {
"name": "$_id",
"count": "$count"
}
}
}
}).pretty()