假设如下聚合查询:

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

哪个返回:

[
{
    "_id": {
        "cluster": 1,
        "idc": "LH5",
        "type": "Virtual"
    },
    "SumCores": 112,
    "SumMemory": 384
},
{
    "_id": {
        "cluster": 1,
        "idc": "LH5",
        "type": "Physical"
    },
    "SumCores": 192,
    "SumMemory": 768
},
{
    "_id": {
        "cluster": 1,
        "idc": "LH8",
        "type": "Virtual"
    },
    "SumCores": 232,
    "SumMemory": 469
},
{
    "_id": {
        "cluster": 1,
        "idc": "LH8",
        "type": "Physical"
    },
    "SumCores": 256,
    "SumMemory": 1024
}
]

有没有办法更改聚合以检索所需的输出:

[
   {
    "_id": {
        "cluster": 1,
        "idc": "LH5"
    },
    "Virtual": {
        "SumCores": 112,
        "SumMemory": 384
    },
    "Physical": {
        "SumCores": 192,
        "SumMemory": 768
    }
},
{
    "_id": {
        "cluster": 1,
        "idc": "LH8"
    },
    "Virtual": {
        "SumCores": 232,
        "SumMemory": 469
    },
    "Physical": {
        "idc": "LH8",
        "type": "Physical"
    }
}
]

假设:

• 每个 IDC/集群总会有一个物理和虚拟“对”

我很高兴收到以下解决方案:

a) 改变聚合查询 b) 接收现有数据并通过库和/或算法将其更改为这种格式

最佳答案

您已经在查询中做了所有正确的事情，因为您需要在您拥有的级别上 $group 以获得正确的总和。唯一剩下的就是将它们整合在一起。

就我个人而言，我会坚持使用数组中的“对”作为最终输出:

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"
            }
        }
    }},
    { "$sort" : { "_id.idc": -1, "_id.cluster": 1 } }
]);

这会给你:

{
        "_id" : {
                "cluster" : 1,
                "idc" : "LH8"
        },
        "data" : [
                {
                        "type" : "Virtual",
                        "SumCores" : 232,
                        "SumMemory" : 469
                },
                {
                        "type" : "Physical",
                        "SumCores" : 256,
                        "SumMemory" : 1024
                }
        ]
}
{
        "_id" : {
                "cluster" : 1,
                "idc" : "LH5"
        },
        "data" : [
                {
                        "type" : "Virtual",
                        "SumCores" : 112,
                        "SumMemory" : 384
                },
                {
                        "type" : "Physical",
                        "SumCores" : 192,
                        "SumMemory" : 768
                }
        ]
}

但如果你真的必须，那么你可以从数组中过滤出匹配的元素并将它们放在它们自己的属性中:

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" : "LH8"
        },
        "Physical" : {
                "SumCores" : 256,
                "SumMemory" : 1024
        },
        "Virtual" : {
                "SumCores" : 232,
                "SumMemory" : 469
        }
}
{
        "_id" : {
                "cluster" : 1,
                "idc" : "LH5"
        },
        "Physical" : {
                "SumCores" : 192,
                "SumMemory" : 768
        },
        "Virtual" : {
                "SumCores" : 112,
                "SumMemory" : 384
        }
}

但第一个只是为您提供相同的基本数据，而不需要额外传递结果。

无论如何，它实际上只是一个额外的$group 将它们组合在一起，然后是可选阶段(如果您真的必须拥有该数据格式)。但我会亲自处理代码中需要处理的任何对“对”的访问。

关于MongoDB - 聚合多行，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/32628218/