If I have a collection like this:
{
"store" : "XYZ",
"total" : 100
},
{
"store" : "XYZ",
"total" : 200
},
{
"store" : "ABC",
"total" : 300
},
{
"store" : "ABC",
"total" : 400
}
I can get the $sum
of orders in the collection by aggregation:
db.invoices.aggregate([{$group: { _id: null, total: { $sum: "$total"}}}])
{
"result": [{
"_id": null,
"total": 1000
}
],
"ok": 1
}
And I can get the $sum
of orders grouped by store:
db.invoices.aggregate([{$group: { _id: "$store", total: { $sum: "$total"}}}])
{
"result": [{
"_id": "ABC",
"total": 700
}, {
"_id": "XYZ",
"total": 300
}
],
"ok": 1
}
But how can I do this in one query?
You could aggregate as below:
$group
by the store
field, calculate the subtotal
.
$project
a field doc
to keep the subtotal
group in tact, during the next
group.
$group
by null
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.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.
Should be the same as in the accepted answer.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With