MongoDB Aggregation - SUM WHERE

Question

I have a large dataset similar to below in mongoDB

I want to run an aggregation in MongoDB which would be the equivalent of this SQL:

SELECT SUM(cores) from machines 
WHERE idc='AMS' AND cluster='1' AND type='Physical';

How do I go about going this in MongoDB?

[
    {
        "_id" : "55d5dc40281077b6d8af1bfa",
        "hostname" : "x",
        "domain" : "domain",
        "description" : "VMWare ESXi 5",
        "cluster" : "1",
        "type" : "Physical",
        "os" : "EXSi",
        "idc" : "AMS",
        "environment" : "DR",
        "deviceclass" : "host",
        "cores" : "64",
        "memory" : "256",
        "mounts" : [ ],
        "roles" : [
                "ESX-HOST"
        ],
        "ipset" : {
                "backnet" : "1"
        },
        "frontnet" : [ ],
        "created" : "2015-09-08T07:35:03.343Z"
    },
    {
        "_id" : "55d5dc40281077b6d8af1bfb",
        "hostname" : "x",
        "domain" : "domain",
        "description" : "VMWare ESXi 5",
        "cluster" : "1",
        "type" : "Physical",
        "os" : "EXSi",
        "idc" : "AMS",
        "environment" : "DR",
        "deviceclass" : "host",
        "cores" : "64",
        "memory" : "256",
        "mounts" : [ ],
        "roles" : [
                "ESX-HOST"
        ],
        "ipset" : {
                "backnet" : "1"
        },
        "frontnet" : [ ],
        "created" : "2015-09-08T07:35:03.346Z"
    }
]

There is a page in the manual with all the common operations as examples: SQL to aggregation Mapping Chart. I suggest you read it — Blakes Seven
– Blakes Seven, Commented Sep 8, 2015 at 7:55
You really don't need aggregation for this. I think what use want is .count because it SUM(_id) doesn't make sense here. — Sede
– Sede, Commented Sep 8, 2015 at 8:06
you do realize that cores is string right? First you need to fix that with an update — Sede
– Sede, Commented Sep 8, 2015 at 8:18

Sede · Accepted Answer · 2015-09-08 08:39:45Z

First you need to update yours documents because cores values is string instead of Number. To do this we use "Bulk" operations.

var bulk = db.machines.initializeOrderedBulkOp(),
    count = 0;
db.machines.find({ "cores": { "$type": 2 }}).forEach(function(doc){
    var cores = parseInt(doc.cores); 
    bulk.find({ "_id": doc._id }).update({     
        "$set": { "cores": cores } }) 
        count++;
        if (count % 200 == 0){  
            // execute per 200 operations and re-init  
            bulk.execute();     
            bulk = db.machines.initializeOrderedBulkOp(); 
        } 
    })

// clean up queues
if (count % 200 != 0)
    bulk.execute();

Then using the aggregation framework we can then get sum of cores. First we need to filter our documents using the $match operator and in the $group stage, we use the $sum operator to get sum of cores values.

db.machines.aggregate([
    { "$match": { "idc": "AMS", "cluster": "1", "type": "Physical" }},
    { "$group": { "_id": null, "sum_cores": { "$sum": "$cores" }}}
])

Which returns:

{ "_id" : null, "sum_cores" : 128 }

Mike Argyriou · Accepted Answer · 2015-09-08 08:00:05Z

0

Although I haven't executed it to test it check this:

db.<collection>.aggregation([
{$match: {
        idc: 'AMS',
        cluster: 1,
        type:'Physical'
    }
},
{$group: {
            _id: null,
            sum: {$sum: "$_id"}
    }
},
{$project: {
        _id:0,
        sum:1
    }
}

])

answered Sep 8, 2015 at 8:00

Mike Argyriou

1,3202 gold badges18 silver badges31 bronze badges

Comments

szimon · Accepted Answer · 2015-09-08 08:39:37Z

0

I think using aggregation framework is impossible, as 'cores' is saved as string, and currently mongo does not allow projecting string as number in $project pipeline phase. Same result using simple javascript:

var tmp = db.cores.find({idc: 'AMS', cluster: '1', type: 'Physical'}, {_id: 0, cores: 1})
var totalCores = 0;
tmp.forEach(function(doc) {
    totalCores += parseInt(doc.cores);
})

print(totalCores)

if I understand the problem correctly.

answered Sep 8, 2015 at 8:39

szimon

7321 gold badge6 silver badges13 bronze badges

Collectives™ on Stack Overflow

MongoDB Aggregation - SUM WHERE

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related