Dynamic value keys in MongoDB

Question

Say I have this:

{ "_id" : "ENVD", "years" : [ { "year" : "2013", "avgInstructor" : 5.144999999999998 }, { "year" : "2012", "avgInstructor" : 5.194436090225564 } ] } 

I need to find the difference in the avgInstructor field from 2012 to 13. I thought that I could convert the keys somehow using $project , which will make the year the key, and the avgInstructor rating will be a value. So it will look like this:

 { "_id" : "ENVD", "years" : { "2013" : 5.144999999999998, "2012" : 5.194436090225564 } } 

Is it possible? Keep in mind that my main goal is to perform a subtraction similar to this pseudo-code: years['2013'].avgInstructor - years['2013'].avgInstructor . Therefore, if you see an easier way, it will be great. I am not sure how best to do this in the context of the aggregation pipeline. Can anyone help?

Answer 1

A Possible answer ...

• Rewind first
• to make it a little easier to handle
• Sort by _id and then by year
• group _id to get the first and last value

• final projection to get a subtracted value

   db.coll.aggregate ( [ { "$unwind" : "$years" } , { $project : { "year" : "$years.year", "avgInstructor" : "$years.avgInstructor" } }, { $sort : { "_id" : 1, "year" : 1 } }, { $group : { "_id" : "$_id", "val_min" : { $first : "$avgInstructor" }, "val_max" : { $last : "$avgInstructor" } } }, { $project : { "diff" : { $subtract : [ "$val_min", "$val_max" ] } } } ] )