Combine multiple groups in an aggregation in mongodb

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.
  • 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 with console.time(), I'd be happy to include the results.


Output

Should be the same as in the accepted answer.