#### Details

#### Description

The following is something I sent to a user, and resembles something

we discuss relatively often with users/customers.

If you feel like documenting this, maybe also see this:

http://www.moschetti.org/rants/mongomoney.html

–

Richard

–

The basic question is how to handle monetary data in MongoDB. There

are a number of tricky aspects to this problem:

(A) Many fractional numeric quantities that come up frequently in

monetary problems have no exact representation in binary floating

point. For example, one tenth and one third have no exact

representation in binary floating point.

(B) Many applications that want to handle monetary data are required

to conform to some kind of regulatory requirements about how

arithmetic works on monetary values (e.g., "banker's rounding" and the

like). Typically, binary floating point arithmetic (such as the stuff

implemented in modern hardware, and that MongoDB uses) does not

conform to applicable regulation for monetary arithmetic.

MongoDB has no built-in support for storing numeric data except as

IEEE double-precision (64-bit) floating point numbers, and as 32-bit

and 64-bit signed integers.

MongoDB has no built-in support for decimal arithmetic, either exact

or inexact, except for arithmetic on 32-bit and 64-bit integers.

So how you handle monetary data in MongoDB depends on the

use case.

Broadly speaking, there are two options:

(1) If you know in advance what the maximum precision for monetary

data is, and you need to be able to do exact, server-side querying,

sorting, and incrementing/decrementing: convert the monetary value to

an integer by scaling by a power of 10 large enough that the smallest

unit of monetary relevance becomes the least significant digit in the

integral value. (For example, if you care about things down to the

tenth of a cent, scale by 1000.)

This approach allows the application to employ all numerical and

arithmetic features MongoDB provides, so long as the application

performs appropriate scaling of operands to these features.

However, note that every part of the application needs to be

consistent about this (that's also true of arithmetic operations in

systems that use fractional values, however).

(2) If you need arbitrary or unanticipated precision in your monetary

data, store two fields: one that encodes the exact value as a

non-numeric MongoDB data type (BinData or string, say), along with a

double-precision floating point approxomation of the exact value for

server-side range and sort queries.

This way, an application that needs to retrieve or sort data according

to the monetary value can, if careful, specify suitable range and sort

specifications to the MongoDB deployment for accessing documents;

applications that do this will also need to do client-side

postprocessing (filtering, say) of documents retrieved based on the

decoding of the opaque representation of the exact value.

In general this approach is somewhat more restrictive than the

previous, because the exact value is stored as an additional object

for which there won't be any numeric operations available. However, if

MongoDB is just being used for storage and retrieval, but never update

or analysis, of monetary data, this approach mostly works.