Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Combine multiple groups in an aggregation in mongodb

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?

like image 524
Matt Kim Avatar asked Feb 09 '15 22:02

Matt Kim


2 Answers

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
        }
    ]
}
like image 66
BatScream Avatar answered Oct 07 '22 22:10

BatScream


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.

like image 30
anarchist912 Avatar answered Oct 07 '22 22:10

anarchist912