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?
Edit:This is different from the question that was marked as a duplicate I still need to group it by status and I cant group by status and get a sum of status in the same group. The duplicate questions is grouping by name and getting a sum of logs based on title which is a sum of an embedded document not a sum off all documents. Status is not embedded it is a singluar property per document.
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,
}
The result I get when I do what the duplicate question says is not the result I am asking for:
/* 1 */
{
"_id" : "Hold",
"Active" : 0.0,
"Closed" : 0.0,
"Pending" : 0.0,
"Hold" : 1.0,
"Not Approved" : 0.0
}
/* 2 */
{
"_id" : "Pending",
"Active" : 0.0,
"Closed" : 0.0,
"Pending" : 235.0,
"Hold" : 0.0,
"Not Approved" : 0.0
}
/* 3 */
{
"_id" : "Not Approved",
"Active" : 0.0,
"Closed" : 0.0,
"Pending" : 0.0,
"Hold" : 0.0,
"Not Approved" : 4199.0
}
/* 4 */
{
"_id" : "Active",
"Active" : 1923.0,
"Closed" : 0.0,
"Pending" : 0.0,
"Hold" : 0.0,
"Not Approved" : 0.0
}
/* 5 */
{
"_id" : "Closed",
"Active" : 0.0,
"Closed" : 20189.0,
"Pending" : 0.0,
"Hold" : 0.0,
"Not Approved" : 0.0
}