我的收藏中有以下结构:

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

我想计算“城市”键在“地址”和“姓名”中出现的次数。

我想查询上面的集合并想要以下输出:

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

最佳答案

我模拟了你的收藏

{
    "_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"
            }
        }
    ]
}

然后我使用 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" : [ 
        {
            "name" : "PQR",
            "city" : "NewYork",
            "count" : 1
        }, 
        {
            "name" : "ABC",
            "city" : "London",
            "count" : 2
        }, 
        {
            "name" : "XYZ",
            "city" : "London",
            "count" : 2
        }
    ],
    "ok" : 1
}

问题后更新

我添加了一个新文档

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

和新查询

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" : [ 
        {
            "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
}

关于mongodb - 如何使用 mongoDB 在一个文档中查找键的计数？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/27224341/