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 } 

Answer 2

Starting Mongo 4.2, there is a new $round aggregation operator which can be used to round a number with a certain precision to a specified decimal place:

{ $round : [ <number>, <place> ] }

Which can be used as such within an aggregation pipeline (here we round xs to 2 decimal places):

// db.collection.insert([{x: 1.23456}, {x: 9.87654}, {x: 0.055543}, {x: 12.345}]) db.collection.aggregate([{ $project: { "rounded_x": { $round: ["$x", 2] }}}]) // [{"rounded_x": 1.23}, {"rounded_x": 9.88}, {"rounded_x": 0.06}, {"rounded_x": 12.35}] 

Note that the place parameter is optional, and omitting it results in rounding to a whole integer (i.e. rounding at 0 decimal places).