Mongodb Count objects attributes

Question

I have a collection named configurations. In configurations collection there are many companies. And each company has workstations, servers, printers, phones.

The schema for configurations is

Configuration: {
    CompanyName: String
    Type: String /// server, phones etc
}

I want output like that

Company : ABC 
Servers : 5
Phones 6
Workstations: 0

Company: XYZ 
Workstations: 9
Servers: 7
Phones: 5

@chridam this is the output http://imgur.com/a/zmQgc using this code https://gist.github.com/anonymous/7139449323c0521e48602a251f57d7e2 — Noman Maqsood, Aug 18 '16 at 06:51
@styvane I do not know why you market it duplicate. The link you posted is different from my problem. I want dynamic count — Noman Maqsood, Aug 18 '16 at 06:52

chridam · Answer 1 · 2016-09-05T13:14:39.877

Use the aggregation framework to take advantage of the $cond operator in the $group pipeline step to evaluate the counts based on the Type field value, something like the following:

db.configurations.aggregate([    
    { 
        "$group": { 
            "_id": "$CompanyName",             
            "server_count": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$Type", "server" ] }, 1, 0 ]
                }
            },
            "phone_count": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$Type", "phone" ] }, 1, 0 ]
                }
            },
            "workstation_count": {
                "$sum": {
                    "$cond": [ { "$eq": [ "$Type", "workstation" ] }, 1, 0 ]
                }
            }
        }  
    },
    {
        "$project": {
            "_id": 0, "Company": "$_id",            
            "Workstations": "$workstation_count",
            "Servers": "$server_count",
            "Phones": "$phone_count",
         }
    }        
])

If you don't know the Type values beforehand and would like to create the pipeline array dynamically, run the distinct command on the that field. This will give you an object that contains a list of the distinct types:

var result = db.runCommand ( { distinct: "configurations", key: "Type" } )
var types = result.values;
printjson(types); // this will print ["server", "phone", "workstation"] etc

Now given the list above, you can assemble your pipeline by creating an object that will have its properties set using JavaScript's reduce() method. The following demonstrates this:

var groupObj = { "$group": { "_id": "$CompanyName" } },
    projectObj = { "$project": { "_id": 0 } };

var groupPipeline = types.reduce(function(obj, type) { // set the group pipeline object 
    obj["$group"][type + "_count"] = {
        "$sum": {
            "$cond": [ { "$eq": [ "$Type", type ] }, 1, 0 ]
        }
    };
    return obj;
}, groupObj );

var projectPipeline = types.reduce(function(obj, type) { // set the project pipeline object 
    obj["$project"][type+'s'] = "$" + type + "_count";
    return obj;
}, projectObj );

Use these two documents in your final aggregation pipeline as:

db.configurations.aggregate([groupPipeline, projectPipeline]);

Check the demo below.

var types = ["server", "workstation", "phone"],
 groupObj = { "$group": { "_id": "$CompanyName" } },
 projectObj = { "$project": { "_id": 0 } };

var groupPipeline = types.reduce(function(obj, type) { // set the group pipeline object 
 obj["$group"][type + "_count"] = {
  "$sum": {
   "$cond": [ { "$eq": [ "$Type", type ] }, 1, 0 ]
  }
 };
 return obj;
}, groupObj );

var projectPipeline = types.reduce(function(obj, type) { // set the project pipeline object 
 obj["$project"][type+"s"] = "$" + type + "_count";
 return obj;
}, projectObj );

var pipeline = [groupPipeline, projectPipeline]

pre.innerHTML = JSON.stringify(pipeline, null, 4);

<pre id="pre"></pre>

-- UPDATE --

From the comments trail, the solution you proposed using promises is not working because you are not returning the promises with the pipeline.

Refactor your code to follow this pattern:

Ticket.distinct("status.name").then(function(result) { 
    var groupPipeline, groupObj, projectObj, projectPipeline;
    groupObj = {
            "$group": {
                "_id": "$company.name" /// company is object
                // name is attribute of company
            }
        },
        projectObj = {
            "$project": {
                "_id": 0
            }
        };

    groupPipeline = result.reduce(function(obj, result) { 
        obj["$group"][result + "_count"] = {
            "$sum": {
                "$cond": [{
                    "$eq": ["$Type", result]
                }, 1, 0]
            }
        };
        return obj;
    }, groupObj);

    projectPipeline = result.reduce(function(obj, result) { 
        obj["$project"][result + 's'] = "$" + result + "_count";
        return obj;
    }, projectObj);

    return [groupPipeline, projectPipeline];

}).then(function(pipeline) {
    return Ticket.aggregate(pipeline).then(function(results) {
        console.log(results);
        return res.json({
            status: true,
            code: 200,
            data: results
        })
    });
})

Can we make it dynamic ? Like we are hard coding it , like server is there anyother way? Whatever user add in type we just count it. Suppose some xyz type user entered which we do not mention so how we can count it thats why i am saying how to dynamic it — Noman Maqsood, Aug 16 '16 at 16:34
this is the output http://imgur.com/a/zmQgc using this code https://gist.github.com/anonymous/7139449323c0521e48602a251f57d7e2 — Noman Maqsood, Aug 18 '16 at 06:50

Mongodb Count objects attributes

1 Answers1