How to Use Decimal Type in Mongodb

MongoDB - What about Decimal type of value?

If you want an exact representation for financial purposes, then doubles or floating point values are unsuitable as the fractional parts are subject to rounding error. Certain decimal values cannot not be represented using binary-based floating points and must be approximated.

For a less technical intro, see The trouble with rounding floating point numbers; if you want to geek out, then read What Every Computer Scientist Should Know About Floating-Point Arithmetic.

The recommendation of using an integer type (storing the value in cents) is to avoid potential rounding errors. This approach is described as "Using a Scale Factor" in the MongoDB documentation for modelling monetary data and is a general workaround for MongoDB 3.2 and earlier.

MongoDB 3.4 includes a new Decimal BSON type which provides exact precision for manipulating monetary data fields.

How to deal with decimals in MongoDB v3.6

The Number type is a floating point numeric representation that cannot accurately represent decimal values. This may be fine if your use case does not require precision for floating point numbers, but would not be suitable if accuracy matters (for example, for fractional currency values).

If you want to store and work with decimal values with accuracy you should instead use the Decimal128 type in Mongoose. This maps to the Decimal 128 (aka NumberDecimal) BSON data type available in MongoDB 3.4+, which can also be manipulated in server-side calculations using the Aggregation Framework.

If your rating field doesn't require exact precision, you could continue to use the Number type. One reason to do so is that the Number type is native to JavaScript, while Decimal128 is not.

For more details, see A Node.js Perspective on MongoDB 3.4: Decimal Type.

How to use decimal type in MongoDB

MongoDB doesn't properly support decimals until MongoDB v3.4. Before this version it stored decimals as strings to avoid precision errors.

Pre v3.4
Store decimals as strings, but this prevents arithmetic operations. Operators as $min, $avg, ... won't be available. If precision is not a big deal, then you might be able to switch to double.

v3.4+
You need to make sure the following preconditions are true:

  • MongoDB server should be at least v3.4.
  • MongoCSharpDriver should be at least v2.4.3.
  • Database should have featureCompatibilityVersion set to '3.4'. If your database has been created by an older MongoDB version and you have upgraded your server to v3.4 your database might still be on an older version.

If you have all the properties set, then register the following serializers to use the decimal128 type:

BsonSerializer.RegisterSerializer(typeof(decimal), new DecimalSerializer(BsonType.Decimal128));
BsonSerializer.RegisterSerializer(typeof(decimal?), new NullableSerializer<decimal>(new DecimalSerializer(BsonType.Decimal128)));

Decimal / Float in mongoose for node.js

I've searched a bit and found this article stating that for storing float values you must use Number type. You can store any float value in speed field.

Need to store high precision decimal values in MongoDB

I will answer your question partially (because I do not know much about C#).

So what will you lose if you will store decimals as strings.

  • your numbers on average would weight more (each double number cost 8 bytes to store which means that every string that has more then 8 chars will weight more). Because of these your indexes (if they will be built on this field would grow)
  • you will not be able to use operators which takes numbers as arguments $inc, $bit, $mod, $min, $max and in 2.6 version $mul. (May be I forgot something)
  • you will not be able to compare numbers (may be '1.65' and '1.23' is comparable as a string, but definitely not numbers with e and minuses somewhere in between). Because of this operations which build on top of comparison like $sort, and all these $gte, $gt, $lte, $lt will not work correctly.

What will you lose in precision if you store decimal as double:

  • based on this, Decimal in C# has 28-29 significant digits, whereas looking at my first link and checking the spec for double precision you see that it has 15-17 significant digits. This is basically what you will lose
  • another really important thing which people sometimes forget when dealing with double floats is illustrated below:

.

db.c.insert({_id : 1, b : 3.44}) 
db.c.update({_id : 1},{$inc : {b : 1}})
db.c.find({b: 4.44}) // WTf, where is my document? There is nothing there

Regarding the 2-nd subquestion:

Is there a way to set a default serialization of decimals as double
(AllowTruncation) without having to put an Attribute on each property?

I do not really understood it, so I hope someone would be able to answer it.

How to convert NumberDecimal to Double in MongoDB shell?

The decimal type is not native to JavaScript, so NumberDecimal values in the shell are special wrappers representing the BSON value stored in MongoDB. If you want to use parseFloat() you can convert a NumberDecimal to JSON in order to access the string value. For example, in your original code this would be: parseFloat(x.test.toJSON()["$numberDecimal"]) .

However, a better approach would be to use the aggregation framework to manipulate decimal values including arithmetic operations (MongoDB 3.4+) and type conversion (MongoDB 4.0+).

MongoDB 4.0+ includes a $toDouble() expression that will convert numeric values (decimal, int, long, boolean, date, string) to a double. The aggregation framework in MongoDB 4.0 cannot be used to update documents (unless you want to create a new collection or replace the existing collection using $out), so you would have to run an aggregation query to convert the values and then separately apply document updates:

// Find matching documents
var docs = db.collection.aggregate([
{ $match: {
test: { $exists: true }
}},

// Add a new field converting the decimal to a double
// (alternatively, the original "test" value could also be replaced)
{ $addFields: {
testDouble: { $toDouble: "$test" }
}}
])

// Update with the changes (Note: this could be a bulk update for efficiency)
docs.forEach(function (doc) {
db.collection.update({ _id: doc._id}, {$set: { testDouble: doc.testDouble }});
});

// Check the results
> db.collection.find().limit(1)
{
"_id" : ObjectId("5d1a202e476381c30cd995a4"),
"test" : NumberDecimal("0.1"),
"testDouble" : 0.1
}

MongoDB 4.2 (currently in RC) adds support for using some aggregation stages for updates, so in 4.2 the above update can be more concisely expressed as:

db.collection.updateMany(
{ test: { $exists: true }},
[ { $addFields: { testDouble: { $toDouble: "$test" }}}]
)


Related Topics



Leave a reply



Submit