MongoDB - double $group make nested a nested

Question

我有一个集合，它在unwind之后具有这样的结构(我删除了我认为与问题无关的信息)

{
        "_id" : ObjectId("1"),
        "Members" : {
                "City" : "New York"
        },
        "Group_name" : "Group A"
}
{
        "_id" : ObjectId("2"),
        "Members" : {
                "City" : "Seattle"
        },
        "Group_name" : "Group A"
}
{
        "_id" : ObjectId("3"),
        "Members" : {
                "City" : "Seattle"
        },
        "Group_name" : "Group A"
}
{
        "_id" : ObjectId("4"),
        "Members" : {
                "City" : "New York"
        },
        "Group_name" : "Group B"
}
{
        "_id" : ObjectId("5"),
        "Members" : {
                "City" : "Los Angeles"
        },
        "Group_name" : "Group B"
}
{
        "_id" : ObjectId("6"),
        "Members" : {
                "City" : "Los Angeles"
        },
        "Group_name" : "Group B"
}

我使用了双对象Id来得到如下结果：

{ "_id" : { "group" : "A", "city" : "New York" }, "totalMembers" : 1 }
{ "_id" : { "group" : "A", "city" : "Seattle" }, "totalMembers" : 2 }
{ "_id" : { "group" : "B", "city" : "New York" }, "totalMembers" : 1 }
{ "_id" : { "group" : "B", "city" : "Los Angeles" }, "totalMembers" : 2 }

我希望能够获得一个具有以下结构的文档：

{
    "_id" : "A",
    "Cities" : {
            "New York" : 1,
            "Seattle" : 2
    }
}
{
    "_id" : "B",
    "Cities" : {
            "New York" : 1,
            "Los Angeles" : 2
    }
}

到目前为止，这是我的代码，我还不能先按“group”分组，然后再按“city”分组。

db.Users_Group.aggregate([
{"$unwind":"$Members"},
{"$group":{"_id":{"group":"$Group_Name","City":"$Members.City"},"totalUsers":{"$sum":1}}},
{"$group":{"_id":"$_id.grupo","total":{"$sum":1}}}
] )

这样，我就可以得到该组中所有成员的总和，而不是按城市分隔。如何将各个城市的文档与每个群组中该城市的总用户进行嵌套？感谢大家在这方面的帮助。提前谢谢。

Answer 1

您需要再运行一次$group，并为$arrayToObject准备数据，这需要一组k-v对：

db.collection.aggregate([
    // your current aggregation stages
    {
        $group: {
            _id: "$_id.group",
            Cities: { $push: { k: "$_id.city", v: "$totalMembers" } }
        }
    },
    {
        $project: {
            _id: 1,
            Cities: { $arrayToObject: "$Cities" }
        }
    }
])

Mongo Playground