mongo aggregation framework with distinct count of key with mutliple group by

Question

I have reviewed several answers and examples relating to the above topic (including on SO), but couldn't find a working example to use answering my requirements:

I have a collection of documents containing several dimensions (descriptive attributes) and metrics. I wish to count the number of appearances (unique) a certain key value has within a certain group of other keys. i.e. how many players arrived from a country and speak a certain language.

Document structure:

{
  "date": "2013-06-13T00:00:00.000Z"
  "Operating System": "Windows 7",
  "Browser": "Chrome",
  "Device": "Desktop/Laptop",
  "Country": "Afghanistan",
  "Language": "English",
  "Player": "91823781188577408" //This is a string value, the player id
},
...

Requested result:

{
  "Country": "Afghanistan",
  "Language": "English",
  "PlayerCount": 120
}

In SQL this would have been something like:

SELECT Country, Language, COUNT(DISTINCT PlayerCount) FROM Table 
GROUP BY Country, Language

My latest attempt with the aggregation involved $project, $group and $unwind of several types, unfortunately, none worked and there's not much value in adding them. Please note that I'm after a solution based on the aggregation framework and not map-reduce.

Many thanks.

Answer 1

Try:

db.YOUR_COLLECTION.aggregate([
    {
        $group: {
            _id: {
                Country: "$Country",
                Language: "$Language"
            },

            PlayerCount: {$sum: 1}
        }
    }
]);

Country and Language will be inside "_id" field.