Condition Group in MongoDB

Question

MongoDB has a number of documents (test events) that look like this:

{ "_id" : ObjectId("5397a78ab87523acb46f56"), "inspector_id" : ObjectId("5397997a02b8751dc5a5e8b1"), "status" : 'defect', "utc_timestamp" : ISODate("2014-06-11T00:49:14.109Z") } { "_id" : ObjectId("5397a78ab87523acb46f57"), "inspector_id" : ObjectId("5397997a02b8751dc5a5e8b2"), "status" : 'ok', "utc_timestamp" : ISODate("2014-06-11T00:49:14.109Z") } 

I need to get a result set that looks like this:

 [ { "date" : "2014-06-11", "defect_rate" : '.92' }, { "date" : "2014-06-11", "defect_rate" : '.84' }, ] 

In other words, I need to get the average level of defect per day. Is it possible?

Answer 1

Aggregation structure is what you want:

 db.collection.aggregate([ { "$group": { "_id": { "year": { "$year": "$utc_timestamp" }, "month": { "$month": "$utc_timestamp" }, "day": { "$dayOfMonth": "$utc_timestamp" }, }, "defects": { "$sum": { "$cond": [ { "$eq": [ "$status", "defect" ] }, 1, 0 ]} }, "totalCount": { "$sum": 1 } }}, { "$project": { "defect_rate": { "$cond": [ { "$eq": [ "$defects", 0 ] }, 0, { "$divide": [ "$defects", "$totalCount" ] } ] } }} ]) 

So, first you group per day using date binding operators and get the totalCount of elements on that day. Using the $cond operator determines whether the "status" is actually a defect or not, and the result is a conditional $sum , where only the values ​​of the "defect" are taken into account.

Once they are grouped per day, you simply $divide result with another check using $cond to make sure that you are not dividing by zero.