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
How to Create a 2D Array from a CSV File
How to Use Decimal Type in Mongodb
Find Item in Observablecollection Without Using a Loop
How to Compare Key/Value Dictionary With == Operator on a Ireadonlycollection<String>
The Find Element Returns Empty String..Using Xpath Contains,Text()
Regular Expression for Valid Filename
User.Identity.Getuserid() Returns Null After Successful Login
Calculate a Checksum for a String
Using Selenium2, How to Check If Certain Text Exists on the Page
How to Generate Unique Number of 8 Digits
Mongodb C# Exception Cannot Deserialize String from Bsontype Int32
How to Read/Write Files in .Net Core
Model Id Property Null in ASP.NET MVC C#
C# Adding Multiple Items to List
Download File With Webclient or Httpclient
Selenium.Webdriver.Chromedriver Slow to Launch - Why
How Does Httpcontext.Current.User.Identity.Name Know Which Usernames Exist