Mongo Aggregation grouping subdocument

Question

I'm kinda stuck doing something seemingly simple with MongoDB's aggregation framework.

Imagine you have documents that would look like this :

[
    { a: 1, b: 2 },
    { a: 1, b: 3 },
    { a: 5, b: 6 }
]

• How can you group documents by the field a and then regroup sub-documents by another field, say b while still calculating the total number of documents at each step ?

For our example, the results would look be the following output document :

{
    results: [
        {
            _id: {
                a: 1
            },
            sum_a: 2,
            doc_a: [
                {
                    _id: {
                        b: 2
                    },
                    sum_b: 1
                },
                {
                    _id: {
                        b: 3
                    },
                    sum_b: 1
                }
            ]
        },
        {
            _id: {
                a: 5
            },
            sum_a: 1,
            doc_a: [
                {
                    _id: {
                        b: 6
                    },
                    sum_b: 1
                }
            ]
        }
    ]
}

I tried things like this :

printjson(db.getSiblingDB('mydb').mycollection.aggregate([
    {
        $project: {
            a: 1,
            b: 1
        }
    },
    {
        $group: {
            _id: {
                a: '$a'
            },
            sum_a: {
                $sum: 1
            },
            b: {
                $first: '$b'
            }
        }
    },
    {
        $group: {
            _id: {
                b: '$b'
            },
            sum_b: {
                $sum: 1
            }
        }
    },
    {
        $sort: {
            sum_a: 1
        }
    }
]));

But in the different tests I made, it keeps overwriting previous group stage results, wrongly calculating sums, ...etc.

So I'm not really sure how to approach this problem.

Answer 1

If you group by main field ('a') and sub-field ('b') together and then group by only 'a' (summing the counts from the first step) and push 'b's into an array (copying counts from the first step), it should produce what you need:

{
    $group : {
        _id : {
            a : '$a',
            b : '$b'
        },
        count : {
            $sum : 1
        }
    }
},{
    $group : {
        _id : {
            a : '$_id.a'
        },
        count_a : {$sum: '$count'},
        doc_a : {
            $push : {
                b : '$_id.b',
                count_b : '$count'
            }
        }
    }
}