CODEWITHSUNDEEP
mongodb
Vishwas
Vishwas

Reputation: 7067

How to find count of a key in one document using mongoDB?

I have following structure in my collection:

users:[
  {
    "name":"ABC",
    "address":{
    "city":"London",
    "country":"UK",
    }
  },
  {
    "name":"XYZ",
    "address":{
      "city":"London",
      "country":"UK",
     }
  },
  {
    "name":"PQR",
    "address":{
    "city":"NewYork",
    "country":"US",
    }
  }
]

I want count of number of occurrences of 'city' key in 'address' and 'name' as a result.

I want to query above collection and want following output:

[{
"name":"ABC",
"city":"London",
"count":2
},{
"name":"XYZ",
"city":"London",
"count":2
}, {
"name":"PQR",
"city":"NewYork",
"count":1
}
]

Upvotes: 2

Views: 94

Answers (1)

Barno
Barno

Reputation: 3341

I simulated your collection

{
    "_id" : ObjectId("547c30ae371ea419f07b9550"),
    "users" : [ 
        {
            "name" : "ABC",
            "address" : {
                "city" : "London",
                "country" : "UK"
            }
        }, 
        {
            "name" : "XYZ",
            "address" : {
                "city" : "London",
                "country" : "UK"
            }
        }, 
        {
            "name" : "PQR",
            "address" : {
                "city" : "NewYork",
                "country" : "US"
            }
        }
    ]
}

And then I use aggregate framework

db.coll.aggregate([

{
  $unwind:"$users"  
},
{
    $group:{
        _id:"$users.address.city",
        name:{$push:"$users.name"},
        city:{$first:"$users.address.city"},
        count:{$sum:1}
    }
},{
    $unwind:"$name"
},{
    $project:{
        _id:0,
        "city":"$_id",
        "name":1,
        "city":1,
        "count":1
    }

}])

result:

{
    "result" : [ 
        {
            "name" : "PQR",
            "city" : "NewYork",
            "count" : 1
        }, 
        {
            "name" : "ABC",
            "city" : "London",
            "count" : 2
        }, 
        {
            "name" : "XYZ",
            "city" : "London",
            "count" : 2
        }
    ],
    "ok" : 1
}

UPDATE AFTER QUESTION

I added a new Document

{
    "_id" : ObjectId("547c394c371ea419f07b9551"),
    "users" : [ 
        {
            "address" : {
                "city" : "Livorno",
                "country" : "LI"
            }
        }, 
        {
            "address" : {
                "city" : "Livorno",
                "country" : "LI"
            }
        }, 
        {
            "address" : {
                "city" : "NewYork",
                "country" : "US"
            }
        }
    ]
}

and new Query

db.coll.aggregate([

{
  $unwind:"$users"  
},
{
    $group:{
        _id:"$users.address.city",        
        "name": {
                $push:{"$ifNull": ["$users.name","$_id"]}
           },
        city:{$first:"$users.address.city"},
        count:{$sum:1}
    }
},{
    $unwind:"$name"
},{
    $project:{
        _id:0,
        "city":"$_id",
        "name":1,
        "city":1,
        "count":1
    }

}])

Result:

{
    "result" : [ 
        {
            "name" : "PQR",
            "city" : "NewYork",
            "count" : 2
        }, 
        {
            "name" : ObjectId("547c394c371ea419f07b9551"),
            "city" : "NewYork",
            "count" : 2
        }, 
        {
            "name" : ObjectId("547c394c371ea419f07b9551"),
            "city" : "Livorno",
            "count" : 2
        }, 
        {
            "name" : ObjectId("547c394c371ea419f07b9551"),
            "city" : "Livorno",
            "count" : 2
        }, 
        {
            "name" : "ABC",
            "city" : "London",
            "count" : 2
        }, 
        {
            "name" : "XYZ",
            "city" : "London",
            "count" : 2
        }
    ],
    "ok" : 1
}

Upvotes: 2

Related Questions