MongoDB $ layout separates computed fields

Question

I am trying to calculate the percentage in a MongoDB query based on computed fields - not sure if this is possible or not. What I would like to do is calculate the percentage of failure: (failed count / total) * 100

Here are some sample documents:

  { "_id" : ObjectId("52dda5afe4b0a491abb5407f"), "type" : "build", "time" : ISODate("2014-01-20T22:39:43.880Z"), "data" : { "buildNumber" : 30, "buildResult" : "SUCCESS" } }, { "_id" : ObjectId("52dd9fede4b0a491abb5407a"), "type" : "build", "time" : ISODate("2014-01-20T22:15:07.901Z"), "data" : { "buildNumber" : 4, "buildResult" : "FAILURE" } }, { "_id" : ObjectId("52dda153e4b0a491abb5407b"), "type" : "build", "time" : ISODate("2014-01-20T22:21:07.790Z"), "data" : { "buildNumber" : 118, "buildResult" : "SUCCESS" } } 

Here is the query I'm trying to work with. The problem is the FailPercent / $ divide line:

 db.col.aggregate([ { $match: { "data.buildResult" : { $ne : null } } }, { $group: { _id: { month: { $month: "$time" }, day: { $dayOfMonth: "$time" }, year: { $year: "$time" }, }, Aborted: { $sum: { $cond : [{ $eq : ["$data.buildResult", "ABORTED"]}, 1, 0]} }, Failure: { $sum: { $cond : [{ $eq : ["$data.buildResult", "FAILURE"]}, 1, 0]} }, Unstable: { $sum: { $cond : [{ $eq : ["$data.buildResult", "UNSTABLE"]}, 1, 0]} }, Success: { $sum: { $cond : [{ $eq : ["$data.buildResult", "SUCCESS"]}, 1, 0]} }, Total: { $sum: 1 }, FailPercent: { $divide: [ "Failure", "Total" ] } } }, { $sort: { "_id.year": 1, "_id.month": 1, "_id.day": 1 } } ]) 

Answer 1

You almost got it. The only change that will be required is that you will need to calculate FailPercent in the additional phase of the project , because the total is only available after the completion of the group phase. Try the following:

 db.foo.aggregate([ { $match: { "data.buildResult" : { $ne : null } } }, { $group: { _id: { month: { $month: "$time" }, day: { $dayOfMonth: "$time" }, year: { $year: "$time" }, }, Aborted: { $sum: { $cond : [{ $eq : ["$data.buildResult", "ABORTED"]}, 1, 0]} }, Failure: { $sum: { $cond : [{ $eq : ["$data.buildResult", "FAILURE"]}, 1, 0]} }, Unstable: { $sum: { $cond : [{ $eq : ["$data.buildResult", "UNSTABLE"]}, 1, 0]} }, Success: { $sum: { $cond : [{ $eq : ["$data.buildResult", "SUCCESS"]}, 1, 0]} }, Total: { $sum: 1 } } }, {$project:{Aborted:1, Failure:1, Unstable:1, Success:1, Total:1, FailPercent: { $divide: [ "$Failure", "$Total" ]}}}, { $sort: { "_id.year": 1, "_id.month": 1, "_id.day": 1 } } ])