MongoDB Aggregation $group and categorise

Question

I have a set of data like this:

> db.esbtrans.find().limit(2).pretty() { "_id" : ObjectId("51fa56a509d013ddbd06f513"), "messageflow" : "TEST", "correlid" : "2b2bdc4f-24bc-412a-8438-9a7e0c256b38", "start" : ISODate("2013-08-01T12:37:57.452Z"), "finish" : ISODate("2013-08-01T12:38:17.452Z"), "response" : NumberLong(20000), "status" : "OK" } { "_id" : ObjectId("51fa56a509d013ddbd06f514"), "messageflow" : "TEST", "correlid" : "0565d123-3570-4ce9-83d7-86e50aad48c5", "start" : ISODate("2013-08-01T12:37:57.452Z"), "finish" : ISODate("2013-08-01T12:38:44.452Z"), "response" : NumberLong(47000), "status" : "ERR" } { "_id" : ObjectId("51fa56a509d013ddbd06f515"), "messageflow" : "TEST2", "correlid" : "d14c447a-eb4c-4a00-85fd-009955798386", "start" : ISODate("2013-08-01T12:37:57.452Z"), "finish" : ISODate("2013-08-01T12:38:57.452Z"), "response" : NumberLong(60000), "status" : "OK" } { "_id" : ObjectId("51fa56a509d013ddbd06f516"), "messageflow" : "TEST2", "correlid" : "3b7902ce-a8bb-496a-a67f-23b562554c16", "start" : ISODate("2013-08-01T12:37:57.452Z"), "finish" : ISODate("2013-08-01T12:38:50.452Z"), "response" : NumberLong(53000), "status" : "ERR" }

Here are two elements of what would be tens of thousands of similar records, the key properties being "messageflow", "status" and a count of the combination. I want to get a result that looks like this:

[{ "messageflow: "TEST", "errors": 1, "successes": 1 },{ "messageflow: "TEST2", "errors": 1, "successes": 1 }]

I have got as far as an Aggregation like this:

> db.esbtrans.aggregate( {"$group": {_id: {messageflow: "$messageflow", status: "$status"}, resptot: {$sum: "$response"}, count: {$sum: 1}}}, {"$project": {flow: "$_id.messageflow", status: "$_id.status", count: "$count", _id: 0}})

Which produces a result like:

 { "result" : [ { "count" : 240, "flow" : "TEST2", "status" : "ERR" }, { "count" : 267, "flow" : "TEST", "status" : "ERR" }, { "count" : 244, "flow" : "TEST", "status" : "OK" }, { "count" : 249, "flow" : "TEST2", "status" : "OK" } ], "ok" : 1 }

However I can't see how I can $project each status ("OK" or "ERR") onto the correct output so that they are fields on a record identified by "messageflow". Any ideas?

Miguel Cartagena · Accepted Answer · 2013-08-01 13:16:59Z

Damo, one thing that you must keep in mind is that when you want to group by a value, you probably have to use $cond operator.

db.esbtrans.aggregate({ $group : { _id : "$messageflow", errors : { $sum : { $cond : [ { $eq : ["$status", "ERR"] } ,1,0] } }, successes : { $sum : { $cond : [ { $eq : ["$status", "OK"] } ,1,0] } }, } })

Explaining: I group by messageflow because this field is your basic axis. Then to count the number of erros and successes, I use the $sum operator in combination with $cond and $eq. It just compares if status is ERR or OK and sum properly.

Great. Thanks Miguel. Getting my head around the Mongo Aggregation framework. Appreciate the help!
What if I didn't have labels of "errors" and "successes" and instead wanted the label to be the status by which it grouped eg. "ERR", "OK" or another status in the collection?

Collectives™ on Stack Overflow

MongoDB Aggregation $group and categorise

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related