CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
sau
sau

Reputation: 1356

display Count of multiple keys in single mongo query

I have got a mongo collection which have following type of data:

{ "_id" : ObjectId("55d5b739f067897648000007"), "did" : "deviceA","key" : "key1"}

I need to get count of each key appeared for a device. I have written following aggregate query:

db.my_collection.aggregate([{'$group':{'_id':{'deviceid':"$did",'keyis':"$key"},'count':{'$sum':1}}}])

I get following result:

{ "_id" : { "deviceid" : "deviceA", "keyis" : "key1" }, "count" : 1 }
{ "_id" : { "deviceid" : "deviceA", "keyis" : "key2" }, "count" : 3 }
{ "_id" : { "deviceid" : "deviceB", "keyis" : "key1" }, "count" : 1 }
{ "_id" : { "deviceid" : "deviceB", "keyis" : "key3" }, "count" : 1 }
{ "_id" : { "deviceid" : "deviceB", "keyis" : "key4" }, "count" : 1 }
{ "_id" : { "deviceid" : "deviceC", "keyis" : "key2" }, "count" : 1 }
{ "_id" : { "deviceid" : "deviceC", "keyis" : "key3" }, "count" : 2 }

What is want is something like this:

{ "_id" : { "deviceid" : "deviceA"}, "count_key1" : 1, "count_key2": 3, "count_key3": 0, "count_key4": 0 }
{ "_id" : { "deviceid" : "deviceB"}, "count_key1" : 1, "count_key2": 0, "count_key3": 1, "count_key4": 1 }
{ "_id" : { "deviceid" : "deviceC"}, "count_key1" : 0, "count_key2": 1, "count_key3": 2, "count_key4": 0 }

Is it possible to do in single mongo query??

Any help appreciated.

Upvotes: 1

Views: 626

Answers (2)

Neo-coder
Neo-coder

Reputation: 7840

You can simply use $sum with $cond in aggregation as :

db.collection.aggregate({
  "$group": {
    "_id": {
      "deviceid": "$did"
    },
    "count_key1": {
      "$sum": {
        "$cond": {
          "if": {
            "$eq": ["$key", "key1"]
          },
          "then": 1,
          "else": 0
        }
      }
    },
    "count_key2": {
      "$sum": {
        "$cond": {
          "if": {
            "$eq": ["$key", "key2"]
          },
          "then": 1,
          "else": 0
        }
      }
    },
    "count_key3": {
      "$sum": {
        "$cond": {
          "if": {
            "$eq": ["$key", "key3"]
          },
          "then": 1,
          "else": 0
        }
      }
    },
    "count_key4": {
      "$sum": {
        "$cond": {
          "if": {
            "$eq": ["$key", "key4"]
          },
          "then": 1,
          "else": 0
        }
      }
    }
  }
})

Upvotes: 2

faisal_kk
faisal_kk

Reputation: 1073

There is no direct way to do it .But we can have a work around for this.

I have created a small subset of data like yours (Sorry for bad formatting):

 > db.q.find()
   { "_id" : ObjectId("55d5b739f067897648000007"), "did" : "deviceA", "key" : "key1" }
   { "_id" : ObjectId("55d5b739f067897648000008"), "did" : "deviceB", "key" : "key1" }
   { "_id" : ObjectId("55d5b739f067897648000009"), "did" : "deviceB", "key" : "key2" }
   { "_id" : ObjectId("55d5b739f067897648000019"), "did" : "deviceA", "key" : "key2" }
   { "_id" : ObjectId("55d5b739f067897648000201"), "did" : "deviceA", "key" : "key2" }

Below query will give the result you wanted :

db.q.aggregate([{
'$group':{'_id':{'deviceid':"$did",'keyis':"$key"},
'count':{'$sum':1}}},{'$group':{'_id':{'deviceid':'$_id.deviceid'},
'counts':{'$push':{'name':'$_id.keyis','count':'$count'}}}},
{'$unwind':'$counts'},{
"$project" : {
    "countKey1" : {
        "$cond" : [
            {
                "$eq" : [
                    "key1",
                    "$counts.name"
                ]
            },
            "$counts.count",
            0
        ]
    },"countKey2" : {
        "$cond" : [
            {
                "$eq" : [
                    "key2",
                    "$counts.name"
                ]
            },
            "$counts.count",
            0
        ]
    }}},{
"$group" : {
    "_id" : "$_id",
    "countKey1" : {
        "$max" : "$countKey1"
    },
    "countKey2" : {
        "$max" : "$countKey2"
    }
}
}])

//Result :

{ "_id" : { "deviceid" : "deviceA" }, "countKey1" : 1, "countKey2" : 2 }
{ "_id" : { "deviceid" : "deviceB" }, "countKey1" : 1, "countKey2" : 1 }

Here I'm assumed you have limited set of keys and values are known at time of writing query.

For more details on this : Blog post on dynamic field names by Asya

Upvotes: 1

Related Questions