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

Therese

you should provide some examples or your data and simplify your question – Nerva

I have updated the question and also provided the data set i used. Thanks amezhenin. – Therese

Why not do it on the client? There's not a natural way to do this in the aggregation framework. You'd need to use map reduce to perform a rounding function. – Johnniejohnny

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 }

Driedup

note that this technically truncates the result to two decimal places. If you want to actually round it you should add 49 before the final divide by 100. – Driedup

Asya, can you please indicate which variable/constant in your first block of code db.a.save({x:1.23456789}).... is actually rounding the number. I am talking like we do in C#, e.g. Math.Round(123.234, 2). In that 2 is the number of decimal places the number will be rounded to. Also can you please modify the above code as per your mentioned comment above (add 49 before the final divide by 100). – Vmail

In steps: given x which is a number, I multiply x by 100 and subtract from that mod of x*100,1 (which is the fractional part after multiplication). That leaves me with integer that's 100x of what I want to keep so then I divide it by 100. 1.5478*100=>154.78 mod of that divided by 1 is .78 so after subtraction I have 154 which I divide by 100. 1.54 left. For different number of decimals after . use different multiple of 10 instead of 100. – Driedup

Note for future readers: as of 3.2 (end of 2015) aggregation framework will have three operators that can be used for this: $trunc which truncates towards 0, $floor and $ceil. – Driedup

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).

Shawn

Note that this will round to even. It wont work for you if you want to round up/down/to zero/to infinity – Goodfornothing

bump on the above. the $round stage has very strange functionality. documentation: mongodb.com/docs/manual/reference/operator/aggregation/round/… – Rainband

Answer 3

I don't know why, but all the answers (at this page) give me 12.34 for 12.345. So I wrote my own project stage:

x = 12.345

{'$project': {
    y: {'$divide': [{'$trunc': {'$add': [{'$multiply': ['$x', 100]}, 0.5]}}, 100]},
}},

It gives 12.35.

---

Here is simple arithmetic, no tricks:

1. 12.345 * 100 = 1234.5

   This step gets us to rounding position: 100 = 10^2 (two signs after dot). Step will be balanced back by step 4.

2. 1234.5 + 0.5 = 1235.0

   Here I get my round half up.

3. truncate(1235.0) = 1235

   Simply drop fractional part.

4. 1235 / 100 = 12.35

However, it doesn't work correctly for negatives (that was enough for my aggregation). For both (positive and negative) cases you should use it with abs:

{'$project': {
    z: {'$multiply': [
        {'$divide': ['$x', {'$abs': '$x'}]}, 
        {'$divide': [{'$trunc': {'$add': [{'$multiply': [{'$abs': '$x'}, 100]}, 0.5]}}, 100]}
    ]},
}}

Here I get number's sign, wrap original number by abs and then multiply sign by rounding output.

Bloom

probably irrelevant to you by now but for posterity, this is because (for some absolutely ridiculous reason), mongo's $round stage rounds to the nearest even number when the digit it checks is 5. see the documentation on its behavior: mongodb.com/docs/manual/reference/operator/aggregation/round/… – Rainband

@Rainband that's really odd behaviour and it's crucial to know, thank you! – Bloom

Answer 4

UPDATE (5/2/2023)

The library is not active maintained, please consider other options.

---

mongo-round works nice.

Say the number is 3.3333333

var round = require('mongo-round');

db.myCollection.aggregate([
    { $project: {
        roundAmount: round('$amount', 2)  // it will become 3.33
    } }
]);

Naquin

Answer 5

This solution correctly rounds up or down to 2dp:

"rounded" : {
  $subtract:[
    {$add:['$absolute',0.0049999999999999999]},
    {$mod:[{$add:['$absolute',0.0049999999999999999]}, 0.01]}
  ]
}

For example it rounds 1.2499 upwards to 1.25, but 1.2501 downwards to 1.25.

Notes:

1. This solution is based on the examples given at http://www.kamsky.org/stupid-tricks-with-mongodb/rounding-numbers-in-aggregation-framework
2. It resolves the problem in Asya Kamsky's answer, that it only truncates and does not round up/down correctly; even after the change suggested in the comments.
3. The number of trailing 9s in the addition factor is large, to accommodate high-precision input numbers. Depending on the precision of the numbers to be rounded, the addition factor may need to be made even more precise than this.

Tourney

Answer 6

There is no round operator in current version of Aggregation Framework. You can try this snippet:

> db.a.save({x:1.23456789})
> db.a.save({x:9.87654321})
> db.a.aggregate([{$project:{y:{$subtract:['$x',{$mod:['$x', 0.01]}]}}}])
{
    "result" : [
        {
            "_id" : ObjectId("51d72eab32549f94da161448"),
            "y" : 1.23
        },
        {
            "_id" : ObjectId("51d72ebe32549f94da161449"),
            "y" : 9.870000000000001
        }
    ],
    "ok" : 1
}

but as you see, this solution doesn't works well because of precision problems. The easiest way in this case is to follow @wiredprairie's advice and make rounds in you application.

Nerva

Answer 7

{$divide:[
            {$cond: { if: { $gte: [ {$mod:[{$multiply:['$dollarAmount',100]}, 1]}, 0.5 ] }, then: {$add: [{$subtract:[
                  {$multiply:['$dollarAmount',100]},
                  {$mod:[{$multiply:['$dollarAmount',100]}, 1]}
          ]}
                ,1]}, else: {$subtract:[
                  {$multiply:['$dollarAmount',100]},
                  {$mod:[{$multiply:['$dollarAmount',100]}, 1]}
          ]} }}
          , 
          100]}

hopefully these one could help in rounding off.

Prudie

Answer 8

rounded:{'$multiply': [{ "$cond": [{ "$gte": [ "$x", 0 ] }, 1,-1 ]},{'$divide': [{'$trunc': {'$add': [{'$multiply': [{'$abs': '$x'}, {$pow:[10,2]}]}, 0.5]}}, {$pow:[10,2]}]}]}

egvo's solution is cool but gives division by zero if it is zero. To avoid $cond may be used to detect sign

(Replace x with field_name and number 2 with desired decimal number)

Matronage

Answer 9

Let me say that it's shame MongoDB is missing this function. I'm hopping they will add it soon.

However, I came up with a lengthy aggregation pipeline. Admitting, it may not be efficient but it honors rules of rounding.

db.t.aggregate([{
    $project: {
        _id: 0,
        number: {
            $let: {
                vars: {
                    factor: {
                        $pow: [10, 3]
                    },
                },
                in: {
                    $let: {
                        vars: {
                            num: {$multiply: ["$$factor", "$number"]},
                        },
                        in: {
                            $switch: {
                                branches: [
                                    {case: {$gte: ["$$num", {$add: [{$floor: "$$num"}, 0.5]}]}, then: {$divide:[ {$add: [{$floor: "$$num"}, 1.0]},"$$factor"]}},
                                    {case: {$lt: ["$$num", {$add: [{$floor: "$$num"}, 0.5]}]}, then: {$divide:[{$floor: "$$num"}, "$$factor"]}}                                    
                                ]
                            }
                        }
                    }
                }
            }
        }
    }
}])

Let's assume, I have following documents in my collection named t

{ number" : 2.341567 }
{ number" : 2.0012 }
{ number" : 2.0012223 }

After running above queries, I got:

{ "number" : 2.342 }
{ "number" : 2.001 }
{ "number" : 2.001 }

Milestone

Answer 10

Watch out for issues with the $round aggregation. When working with double type will sometimes round incorrectly. Here is an example:

5.885 --to double--> 5.8849999999999 ---$round(2)--> 5.88 (instead of 5.89)

Here is a workaround:

> db.a.save({x:5.884})
> db.a.save({x:5.885})
> db.a.save({x:5.886})
> db.a.aggregate([{
     $project:{
         _id:0, 
         x: '$x',
         round: {$round: ['$x', 2]}
         better_round: {$round: [{$add: ['$x', 0.000000001]}, 2]}
     }
}])
Output:
{ "x" : 5.884, "round" : 5.88, "better_round" : 5.88 }
{ "x" : 5.885, "round" : 5.88, "better_round" : 5.89 }
{ "x" : 5.886, "round" : 5.89, "better_round" : 5.89 }

Calandracalandria