43

I am currently learning and applying MongoDB for a small financial related project.

When I read MongoDB in Action, it says:

The only other issue that commonly arises with BSON numeric types is the lack of decimal support. This means that if you’re planning on storing currency values in MongoDB, you need to use an integer type and keep the values in cents.

My financial related product will involve some currency values, but I am little bit confused or worried about the above statement. Here are my questions:

  1. Can I use double for those currency values in my project?
  2. What will happen or is the consequences if I directly use double for them?
  3. If decimal type is an must-have thing for financial product, is that a bad idea to use MongoDB?
  4. What does it mean you need to use an integer type and keep the values in cents? Does it mean that if I am going to store 1.34 dollars, then I should store 134 cents?
Mmmh mmh
  • 5,334
  • 3
  • 21
  • 29
Jackson Tale
  • 25,428
  • 34
  • 149
  • 271

7 Answers7

43

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.

Stennie
  • 63,885
  • 14
  • 149
  • 175
  • Could you please give me an example about `using an integer type (storing the value in cents)` in MongoDB? – Jackson Tale Jul 18 '12 at 15:17
  • 2
    @JacksonTale: There is an example on Kyle's blog: [MongoDB and E-commerce](http://kylebanker.com/blog/2010/04/30/mongodb-and-ecommerce/). This is a few years old (mentions MongoDB 1.5 dev release; current prod release is 2.0 and dev is 2.1) but the general schema concepts apply. Since then there have been many improvements to MongoDB such as better [replication](http://www.mongodb.org/display/DOCS/Replication), [journaling](http://www.mongodb.org/display/DOCS/Journaling) enabled by default, and the upcoming [aggregation framework](http://docs.mongodb.org/manual/applications/aggregation/). – Stennie Jul 18 '12 at 20:41
  • 2
    Support for decimals in e-commerce or financial applications is **very** important. Using integers is a hack at best. Different currencies use a different number of decimal digits so it's impossible just assume that you can use an integer with the last 2 or 4 digits denoting decimals. If in doubt, just google for [Knight Capital](http://en.wikipedia.org/wiki/Knight_Capital_Group#2012_stock_trading_disruption), the company that lost $440M in 45 minutes because of a bug with their integer representation – Panagiotis Kanavos Jan 27 '15 at 08:24
  • "A native Decimal type could take care of the extra formatting for the decimal point, but shouldn't be a significant influence in your decision of which database to use". Seriously? – MatteoSp Feb 24 '16 at 11:35
6

MongoDb added support for Decimal data type in 3.4 version. It is also available from the shell.

3.4 adds support for the decimal128 format with the new decimal data type. The decimal128 format supports numbers with up to 34 decimal digits (i.e. significant digits) and an exponent range of −6143 to +6144.

Unlike the double data type, which only stores an approximation of the decimal values, the decimal data type stores the exact value. For example, a decimal NumberDecimal("9.99") has a precise value of 9.99 where as a double 9.99 would have an approximate value of 9.9900000000000002131628...

Salvador Dali
  • 214,103
  • 147
  • 703
  • 753
  • Note that if using NodeJS, you don't use NumberDecimal(string) directly, you instead use Decimal128.fromString(string). See my answer below for references, example code, etc. – amota Feb 14 '22 at 06:40
4

When you don't want to store currency as cent-values, you could store a currency of $1.34 as an object like this:

{
    major: 1,
    minor: 34,
    currency: "USD"
}

Doing any math with objects like this would not be easy and would not use commercial rounding rules. But you shouldn't do any business logic on the database anyway, especially not when it's a "dumb" database like MongoDB.

What you should be doing is serialize/deserialize these objects from/to a Money class in your application which implements the basic currency math operations respecting the rounding rules and throws an exception when you try to do an operation with different currency units ($12.34 + 14.95€ = error - must convert one currency to the other currency first by providing an exchange rate).

Philipp
  • 67,764
  • 9
  • 118
  • 153
  • 1
    Especially in branches like "promotional products" you will encounter amounts like $0.00005 per item. Then "keep the values in cents" does not hold up. Then you need some datatype like DECIMAL(12,6) in MySQL or split the major and minor parts as Philipp does and add a third item 'diviser' that alows you to specify that there are zeros in the 'minor' part. Another option is {"amount":123305,"precision":4} indicating a value of 12.3305. You just divide the amount by 10^4 – Niek Oost Feb 18 '14 at 10:22
4

If you're using Mongoose then you can use getter/setter functions in the schema definition e.g.

function getDecimalNumber(val) {    return (val/1000000); }
function setDecimalNumber(val) {    return (val*1000000); }

Applicable to a schema object such as

balance: { type: Number, default: 0, get: getDecimalNumber, set: setDecimalNumber },

The number of zeroes to multiply/divide by depends on the accuracy you want.

HNyang
  • 41
  • 1
4

Looks like MongoDB has finally added support for decimals, although at the time of writing this is just finished development, but hopefully it should be available very soon in stable version (3.4?).

https://jira.mongodb.org/browse/SERVER-1393

arva
  • 2,384
  • 1
  • 17
  • 14
3

I know this post is old but it ranks high on Google so...

The best solution for storing financial data is using exact precision as documented by MongoDB themselves here http://docs.mongodb.org/v2.6/tutorial/model-monetary-data/#monetary-value-exact-precision.

{price: 9990, currency: "USD" }

And when you need the data just divide by 100 assuming you want 2 digit precision. The downside is that you always need to work with the same precision.

heavenleft
  • 61
  • 9
  • Not a huge thing, but Mongo technically mentions scaling by 1000. You can obviously scale by whatever you want as it's consistent. – conrad10781 Mar 08 '16 at 20:27
3

NodeJS Users:

Note that if using NodeJS, you don't use NumberDecimal(string) directly, you instead use Decimal128.fromString(string).

References:

Example:

const Decimal128 = require('mongodb').Decimal128

usersCollection.findOneAndUpdate({_id: ObjectID(this.data.userId), "myWells.wellId": ObjectID(this.data.wellId)},
  { 
    $set: { "myWells.$.interest": Decimal128.fromString(this.data.interest) }
  }
)
amota
  • 141
  • 8