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

aggregation-framework, mongodb

Solution

Try:

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

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

Country and Language will be inside "_id" field.

Problem

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.

Original source