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!

Sorry in that code it should read "class" instead of "background", the column name is actually "background" but I chose "class" for brevity I just failed at consistency. — broquaint, May 21 '13 at 14:53

micnil · Answer 1 · 2017-12-03T13:50:15.023

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.

score 2 · Accepted Answer · answered May 20 '13 at 04:42

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
}

there are different answers possible if you know the complete set of possible races and classes - in that case it's easy to generate exact format that you have in your question. — Asya Kamsky, May 20 '13 at 04:42
When I was finally ready to post an answer, I saw that you already did... and 40 minutes faster... and with two commands less. ;) — vinipsmaker, May 20 '13 at 05:29
Just call me The Master of Aggregation Framework. Er, I mean Mistress. If it can be done with it ... I've probably done it. — Asya Kamsky, May 20 '13 at 12:19
That works perfectly, thank you very much, @AsyaKamsky! Now I am left to the enviable task of understanding how it works :) — broquaint, May 21 '13 at 14:42
@AsyaKamsky: It answers the question, but is that really a good idea? It seems quite inefficient. Is it not better practically-speaking to just run two simple $group aggregations? And I can't imagine adding a 3rd or 4th value to the counts using this technique. — Scott Stafford, Oct 01 '13 at 02:04
Is it a good idea or not depends on the requirements of the original author/use case. — Asya Kamsky, Oct 02 '13 at 00:10

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

2 Answers2

Linked