Rounding to 2 decimal places using MongoDB aggregation framework

Question

I am using the mongodb aggregation framework and doing some calculations as shown below

db.RptAgg.aggregate( 
{ $group :
 { _id : {Region:"$RegionTxt",Mth:"$Month"},           
   ActSls:{$sum:"$ActSls"},
   PlnSls:{$sum:"$PlnSls"}
 } 
},
{ $project : 
 {
   ActSls:1,
   PlnSls:1,
   ActToPln:{$cond:[{ $ne: ["$PlnSls", 0] },{$multiply:[{$divide: ['$ActSls', '$PlnSls']},100]},0]}
  }

}

); 

I am trying to figure out what is the best and easiest way to round my results to 2 decimal places. Following is my result

{
    "result" : [
            {
                    "_id" : {
                            "Region" : "East",
                            "Mth" : 201301
                    },
                    "ActSls" : 72,
                    "PlnSls" : 102,
                    "ActToPln" : 70.58823529411765
            }
    ],
    "ok" : 1

}

I want "ActToPln" to show 70.59 instead of "ActToPln" : 70.58823529411765, in the results from aggegation framework itself. I want to avoid doing the rounding in my application

Can you please help with the same.

Following is the dataset i used.

{
    "_id" : ObjectId("51d67ef69557c507cb172572"),
    "RegionTxt" : "East",
    "Month" : 201301,
    "Date" : "2013-01-01",
    "ActSls" : 31,
    "PlnSls" : 51
}
{
    "_id" : ObjectId("51d67ef69557c507cb172573"),
    "RegionTxt" : "East",
    "Month" : 201301,
    "Date" : "2013-01-02",
    "ActSls" : 41,
    "PlnSls" : 51
}

Thanks in advance. Nandu

Answer 1

There is no $round operator but you can do this in the aggregation framework - doing it in specific order will usually avoid floating point precision issues.

> db.a.save({x:1.23456789})
> db.a.save({x:9.87654321})
> db.a.aggregate([{$project:{ _id:0, 
         y:{$divide:[
              {$subtract:[
                      {$multiply:['$x',100]},
                      {$mod:[{$multiply:['$x',100]}, 1]}
              ]},
              100]}
}}])
{ "y" : 1.23 }
{ "y" : 9.87 }

Given the existing pipeline in the problem, replace:

{$multiply:[{$divide: ['$ActSls', '$PlnSls']},100]}

with

{$divide:[
     {$subtract:[ 
          {$multiply:[
             {$divide: ['$ActSls','$PlnSls']},
             10000
          ]}, 
          {$mod:[
             {$multiply:[{$divide: ['$ActSls','$PlnSls']}, 10000 ]},
             1]}
          ]}, 
     100
]}

With your sample data points this is the result:

{ "ActSls" : 31, "PlnSls" : 51, "ActToPln" : 60.78 }
{ "ActSls" : 41, "PlnSls" : 51, "ActToPln" : 80.39 }
{ "ActSls" : 72, "PlnSls" : 102, "ActToPln" : 70.58 }