Thursday, 27 April 2017

Mongodb Group and Count Where Column is Status

I am trying to run a mongo query to group all of my records by status. I then want to get a count per status. I have managed to do this with a $group query but now I would like to take it a step further and make the property name reflect the status and the value equal the count. Is there a way I can accomplish this?

Mongo Query:

db.getCollection('winapplications').aggregate([
    { $match: { 'WINNbr': { '$exists': true, '$ne': '' } } },
    { $match: {"Status":{"$ne":"Approved"}}},
    { $match: {"Status":{"$ne":""}}},
    { $match: {"Status":{"$ne":null}}},
    {
        $group: {
           _id: "$Status",
            count:{$sum:1}
        } 
    }
])

Result:

{
    "_id" : "Hold",
    "count" : 1.0
}

{
    "_id" : "Pending",
    "count" : 235.0
}

{
    "_id" : "Not Approved",
    "count" : 4199.0
}

{
    "_id" : "Active",
    "count" : 1923.0
}

{
    "_id" : "Closed",
    "count" : 20189.0
}

What I would like:

{
    "Hold" : 1
}

{
    "Pending" : 235
}

{
    "Not Approved" : 4199
}

{
    "Active" : 1923
}

{
    "Closed" : 20189,
}



via Ohjay44

No comments:

Post a Comment