Dynamic keys from values in MongoDB

Say I have this:

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

I need to be able to find the difference in the avgInstructor field from 2012-13. I was thinking I could transform the keys somehow using a $project which would make the year be the key, and the avgInstructor rating be the value. So it would look like this:

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

}

Is this possible? Keep in mind my main goal is to be able to run a subtraction like this pseudocode : years['2013'].avgInstructor - years['2013'].avgInstructor. So if you see an easier way, that would be great as well. I am not sure of the best way to go about this in the context of an aggregation pipeline. Can someone help?

Answers


A Possible answer ...

  1. Unwind first
  2. project to make it a little easier to handle
  3. Sort on _id and then on year
  4. group by _id to pickup the first and the last value
  5. final projection to get 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" ] }  } }
    
    ] )
    

Need Your Help

Java - Custom Hash Map/Table Some Points

java hashmap hashtable

In some previous posts I have asked some questions about coding of Custom Hash Map/Table in java. Now as I can't solve it and may be I forgot to properly mentioning what I really want, I am summari...

PHPExcel Pie Chart

php codeigniter phpexcel pie-chart

I've been stuck on this problem for a while now. I still can't seem to be able to figure out the way. I couldn't get the PHPExcel's piechart example, 33chartcreate-pie.php. I searched everywhere, b...