CODEWITHSUNDEEP
databasemongodbmongodb-queryaggregation-frameworknosql-aggregation
Clark Hinchcliff
Clark Hinchcliff

Reputation: 31

In mongo DB, how do I grab multiple counts in a single query?

I'm currently trying to massage out counts from the mLab API for reasons I don't have control over. So I want to grab the data I need from there in one query so I can limit the amount of API calls.

Assuming that my data looks like this:

{
"_id": {
    "$oid": "12345"
},
"dancer": "Beginner",
"pirate": "Advanced",
"chef": "Mid",
"beartamer": "Mid",
"swordsman": "Mid",
"total": "Mid"
}

I know I can do 6 queries with something similar to:

db.score.aggregate({"$group": { _id: {"total":"$total"}, count: {$sum:1} }} )

but how do I query to get the count for each key? I'd like to see something akin to:

{ "_id" : { "total" : "Advanced" }, "count" : 1 }
{ "_id" : { "total" : "Mid" }, "count" : 1 }
{ "_id" : { "total" : "Beginner" }, "count" : 4 }
{ "_id" : { "pirate" : "Advanced" }, "count" : 1 }
//...etc

Upvotes: 0

Views: 109

Answers (1)

dnickless
dnickless

Reputation: 10918

The following should give you precisely what you want:

db.scores.aggregate({
    $project: {
        "_id": 0 // get rid of the "_id" field since we do not want to count it
    }
}, {
    $project: {
        "doc": {
            $objectToArray: "$$ROOT" // transform all documents into key-value pairs
        }
    }
}, {
    $unwind: "$doc" // flatten the resulting array into separate documents
}, {
    $group: {
        "_id": "$doc", // group by distinct key-value combination
        "count": { $sum: 1 } // count documents per bucket
    }
}, {
    $project: {
        "_id": { // some more transformation magic to recreate the desired output structure
            $mergeObjects: [
                { $arrayToObject: [ [ "$_id" ] ] },
                { "count": "$count" }
            ]
        },
    }
}, {
    $replaceRoot: {
        "newRoot": "$_id" // this moves the contents of the "_id" field to the root of the documents
    }
})

Upvotes: 2

Related Questions