CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Rahul Raj
Rahul Raj

Reputation: 3459

MongoDB - How to Truncate Float Values to One or Two decimal points in aggregate?

Let's say this is the query:

db.movies.aggregate([
  {$match: {languages: "English", cast:{$exists:true}}},

  {$unwind: "$cast"},

  {$group: {_id: "$cast", numFilms:{$sum: 1}, average:{$avg: "$imdb.rating"}   
  }},

  {$sort: {numFilms:-1}},

  {$limit: 1}
])

And I get the output:

{ "_id" : "John Wayne", "numFilms" : 107, "average" : 6.424299065420561 }

How can I truncate average to single/double decimal points? I wanted to see an output like this:

{ "_id" : "John Wayne", "numFilms" : 107, "average" : 6.42 }

I tried this post, but seems like I can't get away doing the same with $avg operator. How can I achieve this?

Potential duplicate

I believe the above linked isn't a duplicate, because mine deals with $avg operator.

Upvotes: 3

Views: 6289

Answers (2)

vishal sharma
vishal sharma

Reputation: 49

$project : {
    artistAmt : {
        $divide: [{
            $trunc: {
                $multiply: ["$earnedAmount", 100]
            }
        }, 100]
    }
}    
before : "artistAmt": 1361.8100000000002
after : "artistAmt": 1361.81

Upvotes: 0

Kevin Smith
Kevin Smith

Reputation: 14436

You can achieve this with a $project stage with a $trunc but $multiply before and $divide after to deal with the decimal places.

For example if we add the following:

db.test.insertMany([{
    number: 123.456
}, {
    number: 456.789
}, {
    number: 123
}]);

We can run an aggregation with the following:

db.test.aggregate([{
    $project: {
        number: {
            $divide: [{
                $trunc: {
                    $multiply: ["$number", 100]
                }
            }, 100]
        }
    }
}])

Which will give us:

{ "_id" : ObjectId("5ab23dcb8094b7e4427b43f3"), "number" : 123.45 }
{ "_id" : ObjectId("5ab23dcb8094b7e4427b43f4"), "number" : 456.78 }
{ "_id" : ObjectId("5ab23dcb8094b7e4427b43f5"), "number" : 123 }

Upvotes: 6

Related Questions