MongoDB - Complex Grouping Query

Question

I have the following MongoDB aggregation query which groups by IDC, type and cluster - which works perfectly.

I would like to additionally group "environment", inside this existing grouping. Please see my query below, my existing output, and what I would like to see (desired output).

If you have any questions or wish to see the source (I didn't think it was necessary, as it would take up room on the question, then please comment).

Thanks

Example Source (around 1000 documents):

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

Query:

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 } }
]);

Which gives me the following output:

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

My desired output is:

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

Essentially, I want to group the sums based on the environment

Very nice of you to tell us what you expect as output. Unfortunately your question does not inform anyone as to what the "source" data looks like. Therefore making it impossible for anyone to advise on how to get to your desired result. I suggest adding a sample of what the data looks like at the "source" if you expect some assistance with this problem. — Blakes Seven
– Blakes Seven, Commented Sep 28, 2015 at 10:59

Community · Accepted Answer · 2017-05-23 12:29:38Z

Very much as in your initial query ( actually written by myself ), all you really need to do is add in that field detail to the initial _id of $group and then carry that through into the subsequent array entries:

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 } }
]);

But you also "really" should be using the query form I recommended you did in the first place, since it is clear that all you want to do is diplay this in a template, and looping array content should be very simple:

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 } }
]);

Collectives™ on Stack Overflow

MongoDB - Complex Grouping Query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related