Is it possible to group and sum multiple columns with MongoDB's aggregation framework?

Question

Given this MongoDB collection:

[
  { character: 'broquaint', race: 'Halfling', class: 'Hunter' },
  { character: 'broquaint', race: 'Halfling', class: 'Hunter' },
  { character: 'broquaint', race: 'Halfling', class: 'Rogue' },
  { character: 'broquaint', race: 'Naga',     class: 'Fighter' },
  { character: 'broquaint', race: 'Naga',     class: 'Hunter' }
]

I would like to get a count of each race and class i.e

{
  race:  { 'Halfling': 3, 'Naga': 2 },
  class: { 'Hunter': 3, 'Rogue': 1, 'Fighter': 1 }
}

And I've been trying to do this using the aggregation framework (to replace an existing map/reduce) but have only been able to get as far as getting counts for the combinations i.e

{ '_id': { race: 'Halfling', class: 'Hunter' },  count: 2 }
{ '_id': { race: 'Halfling', class: 'Rogue' }    count: 1 }
{ '_id': { race: 'Naga',     class: 'Fighter' }, count: 1 }
{ '_id': { race: 'Naga',     class: 'Hunter' },  count: 1 }

Which is simple enough to reduce programmatically to the desired result but I was hoping to be able to leave that to MongoDB.

For reference here's the code I have so far:

db.games.aggregate(
  { '$match': { character: 'broquaint' } },
  {
    '$group': {
      _id:   { race: '$race', background: '$background'},
      count: { '$sum': 1 }
    }
  }
)

So the question is - given that example collection can I arrive at my desired output purely through MongoDB's aggregation framework?

For any help that might be rendered many thanks in advance!

Answer 1

Since MongoDB 3.4 this can be achieved a bit simpler using multiple aggregation pipelines with $facet.

taken from the docs:

$facet

Processes multiple aggregation pipelines within a single stage on the same set of input documents. Each sub-pipeline has its own field in the output document where its results are stored as an array of documents.

So, for your use case, this would be achieved by the following:

const aggregatorOpts = [
    { $match: { character: 'broquaint' } }, // Match the character
    {
        // Seperate into 2 or more pipes that will count class and
        // race seperatly
        $facet: {
            race: [
                // Group by race and get the count:
                // [
                //   {
                //     _id: 'Halfling',
                //     count: 3
                //   }
                //   {
                //     _id: 'Naga',
                //     count: 2
                //   }
                // ]

                // $sortByCount is the same as
                // { $group: { _id: <expression>, count: { $sum: 1 } } },
                // { $sort: { count: -1 } }

                { $sortByCount: '$race' },

                // Now we want to transform the array in to 1 document,
                // where the '_id' field is the key, and the 'count' is the value.
                // To achieve this we will use $arrayToObject. According the the
                // docs, we have to first rename the fields to 'k' for the key,
                // and 'v' for the value. We use $project for this:
                {
                    $project: {
                        _id: 0,
                        k: '$_id',
                        v: '$count',
                    },
                },
            ],
            // Same as above but for class instead
            class: [
                { $sortByCount: '$class' },
                {
                    $project: {
                        _id: 0,
                        k: '$_id',
                        v: '$count',
                    },
                },
            ],
        },
    },
    {
        // Now apply the $arrayToObject for both class and race.
        $addFields: {
            // Will override the existing class and race arrays
            // with their respective object representation instead.
            class: { $arrayToObject: '$class' },
            race: { $arrayToObject: '$race' },
        },
    },
];

db.races.aggregate(aggregatorOpts)

Which produces the following:

[
  {
    "race": {
      "Halfling": 3,
      "Naga": 2
    },
    "class": {
      "Hunter": 3,
      "Rogue": 1,
      "Fighter": 1,
    }
  }
]

If you are happy with the output formatting provided @Asya, then you can remove the $projectand $addFieldsstages, and just leave the $sortByCount part in each sub-pipeline.

With these new features, the aggregation is a lot easier to extend with additional counts, Just add another aggregation pipeline in $facet. It is even a bit easier to count sub groups, but that would be a separate question.

Answer 2

Yes, you can do this with aggregation framework. It won't be pretty, but then it'll still be much faster than with mapreduce...

Here it is in a nutshell (output a different format than what you give but same content):

> group1 = {
    "$group" : {
        "_id" : "$race",
        "class" : {
            "$push" : "$class"
        },
        "count" : {
            "$sum" : 1
        }
    }
};
> unwind = { "$unwind" : "$class" };
> group2 = {
    "$group" : {
        "_id" : "$class",
        "classCount" : {
            "$sum" : 1
        },
        "races" : {
            "$push" : {
                "race" : "$_id",
                "raceCount" : "$count"
            }
        }
    }
};
> unwind2 = { "$unwind" : "$races" };
> group3 ={
    "$group" : {
        "_id" : 1,
        "classes" : {
            "$addToSet" : {
                "class" : "$_id",
                "classCount" : "$classCount"
            }
        },
        "races" : {
            "$addToSet" : "$races"
        }
    }
};
> db.races.aggregate(group1, unwind, group2, unwind2, group3);
{
    "result" : [
        {
            "_id" : 1,
            "classes" : [
                {
                    "class" : "Fighter",
                    "classCount" : 1
                },
                {
                    "class" : "Hunter",
                    "classCount" : 3
                },
                {
                    "class" : "Rogue",
                    "classCount" : 1
                }
            ],
            "races" : [
                {
                    "race" : "Naga",
                    "raceCount" : 2
                },
                {
                    "race" : "Halfling",
                    "raceCount" : 3
                }
            ]
        }
    ],
    "ok" : 1
}