MongoDB -复杂分组查询

Question

我有下面的MongoDB聚合查询，哪些组由IDC、类型和集群组成--这是非常完美的。

我想另外分组“环境”，在内这个现有的分组。请看我下面的查询，我现有的输出，以及我想看到的(期望的输出)。

如果您有任何问题或希望看到来源(我不认为这是必要的，因为它将占用空间，然后请评论)。

谢谢

资料来源(约1 000份文件)：

   {
        "_id":"55d5dc40281077b6d8af1bfa",
        "hostname":"1",
        "domain":"domain",
        "description":"VMWare ESXi 5",
        "cluster":1,
        "type":"Physical",
        "os":"EXSi",
        "idc":"AMS",
        "environment":"DR",
        "deviceclass":"host",
        "cores":64,
        "memory":256,
        "clusters":0,
        "customer":"MnS",
        "mounts":[],
        "roles":["ESX-HOST"],
        "ipset":{"backnet":"1"},
        "frontnet":[],
        "created":"2015-09-28T11:12:36.526Z"
    }

查询：

Machine.aggregate([ 
{ "$match": { 
    "idc": req.query.idc, "customer": req.query.customer}
} ,
{ "$group": { 
    "_id": {
        "cluster": "$cluster",
        "idc":"$idc",
        "type": "$type"
    },
    "SumCores": { "$sum":"$cores" },
    "SumMemory": { "$sum":"$memory" }
}},
{ "$group": {
    "_id": {
        "cluster": "$_id.cluster",
        "idc": "$_id.idc"
    },
    "data": {
        "$push": {
            "type": "$_id.type",
            "SumCores": "$SumCores",
            "SumMemory": "$SumMemory"
        }
    }
}},
{ "$project": {
    "Physical": {
        "$setDifference": [
            { "$map": {
                "input": "$data",
                "as": "el",
                "in": {
                    "$cond": [
                        { "$eq": [ "$$el.type", "Physical" ] },
                        {
                            "SumCores": "$$el.SumCores",
                            "SumMemory": "$$el.SumMemory"
                        },
                        false
                    ]
                }
            }},
            [false]
        ]
    },
    "Virtual": {
        "$setDifference": [
            { "$map": {
                "input": "$data",
                "as": "el",
                "in": {
                    "$cond": [
                        { "$eq": [ "$$el.type", "Virtual" ] },
                        {
                            "SumCores": "$$el.SumCores",
                            "SumMemory": "$$el.SumMemory"
                        },
                        false
                    ]
                }
            }},
            [false]
        ]
    }
}},
{ "$unwind": "$Physical" },
{ "$unwind": "$Virtual"},
{ "$sort" : { "_id.idc": -1, "_id.cluster": 1 } }
]);

这给了我以下输出：

{
    "_id" : {
            "cluster" : 1,
            "idc" : "LH5"
    },
    "Physical" : {
            "SumCores" : 192,
            "SumMemory" : 768
    },
    "Virtual" : {
            "SumCores" : 112,
            "SumMemory" : 384
    }
}

我想要的输出是：

[
{
    "_id": {
        "cluster": 1,
        "idc": "LH8"
    },
    "Physical": [
        {
            "environment": "DR",
            "SumCores": 256,
            "SumMemory": 1024
        },
        {
            "environment": "PROD",
            "SumCores": 256,
            "SumMemory": 1024
        }
    ],
    "Virtual": [
        {
            "environment": "DR",
            "SumCores": 232,
            "SumMemory": 469
        },
        {
            "environment": "PROD",
            "SumCores": 232,
            "SumMemory": 469
        }
    ]
}
]

本质上，我想根据环境将这些总和分组。

Answer 1

就像在初始查询( actually written by myself )中一样，您真正需要做的就是将该字段的详细信息添加到初始_id of $group中，然后将其执行到后续的数组条目中：

Machine.aggregate([ 
    { "$match": { 
        "idc": req.query.idc, "customer": req.query.customer}
    } ,
    { "$group": { 
        "_id": {
            "cluster": "$cluster",
            "idc":"$idc",
            "type": "$type",
            "environment": "$environment"
        },
        "SumCores": { "$sum":"$cores" },
        "SumMemory": { "$sum":"$memory" }
    }},
    { "$group": {
        "_id": {
            "cluster": "$_id.cluster",
            "idc": "$_id.idc"
        },
        "data": {
            "$push": {
                "type": "$_id.type",
                "environment": "$_id.environment",
                "SumCores": "$SumCores",
                "SumMemory": "$SumMemory"
            }
        }
    }},
    { "$project": {
        "Physical": {
            "$setDifference": [
                { "$map": {
                    "input": "$data",
                    "as": "el",
                    "in": {
                        "$cond": [
                            { "$eq": [ "$$el.type", "Physical" ] },
                            {
                                "environment": "$$el.environment",
                                "SumCores": "$$el.SumCores",
                                "SumMemory": "$$el.SumMemory"
                            },
                            false
                        ]
                    }
                }},
                [false]
            ]
        },
        "Virtual": {
            "$setDifference": [
                { "$map": {
                    "input": "$data",
                    "as": "el",
                    "in": {
                        "$cond": [
                            { "$eq": [ "$$el.type", "Virtual" ] },
                            {
                                "environment": "$$el.environment",                                    
                                "SumCores": "$$el.SumCores",
                                "SumMemory": "$$el.SumMemory"
                            },
                            false
                        ]
                    }
                }},
                [false]
            ]
        }
    }},
    { "$unwind": "$Physical" },
    { "$unwind": "$Virtual"},
    { "$sort" : { "_id.idc": -1, "_id.cluster": 1 } }
]);

但是，您还应该使用我建议您首先使用的查询表单，因为很明显，您要做的就是在模板中播放这个内容，并且循环数组内容应该非常简单：

Machine.aggregate([ 
    { "$match": { 
        "idc": req.query.idc, "customer": req.query.customer}
    } ,
    { "$group": { 
        "_id": {
            "cluster": "$cluster",
            "idc":"$idc",
            "type": "$type",
            "environment": "$environment"
        },
        "SumCores": { "$sum":"$cores" },
        "SumMemory": { "$sum":"$memory" }
    }},
    { "$group": {
        "_id": {
            "cluster": "$_id.cluster",
            "idc": "$_id.idc"
        },
        "data": {
            "$push": {
                "type": "$_id.type",
                "environment": "$_id.environment",
                "SumCores": "$SumCores",
                "SumMemory": "$SumMemory"
            }
        }
    }},
    { "$sort" : { "_id.idc": -1, "_id.cluster": 1 } }
]);