Combine multiple groups in an aggregation in mongodb
You could aggregate as below:
$group
by thestore
field, calculate thesubtotal
.$project
a fielddoc
to keep thesubtotal
group in tact, during the next group.$group
bynull
and accumulate the net total.
Code:
db.invoices.aggregate([{
$group: {
"_id": "$store",
"subtotal": {
$sum: "$total"
}
}
}, {
$project: {
"doc": {
"_id": "$_id",
"total": "$subtotal"
}
}
}, {
$group: {
"_id": null,
"total": {
$sum: "$doc.total"
},
"result": {
$push: "$doc"
}
}
}, {
$project: {
"result": 1,
"_id": 0,
"total": 1
}
}
])
Output:
{
"total": 1000,
"result": [{
"_id": "ABC",
"total": 700
}, {
"_id": "XYZ",
"total": 300
}
]
}
Another approach would be using the $facet
aggregation stage.
$facet
allows you to do multiple nested sub-aggregations within your main aggregation.- Each sub-aggregation has its own pipeline.
- For each result of a sub-aggregation we define another field.
Like this, for example:
db.invoices.aggregate([
{
$facet: {
total: [
{
$group: {
_id: null,
total: { $sum: "$total"}
}
}
],
store_totals: [
{
$group: {
_id: "$store",
total: { $sum: "$total"}
}
}
]
}
},{
$unwind: "$total"
},{
$project: {
_id: 0,
total: "$total.total",
store_totals: "$store_totals"
}
}
]
@BatScream wrote, that an
$unwind
stage might be costly. However we're unwinding an array of length 1 here. So I'm curious which approach is more efficient under which circumstances. If someone can compare those withconsole.time()
, I'd be happy to include the results.
Output
Should be the same as in the accepted answer.