Model Monetary Data
On this page
Overview
Applications that handle monetary data often require the ability to capture fractional units of currency and need to emulate decimal rounding with exact precision when performing arithmetic. The binary-based floating-point arithmetic used by many modern systems (i.e., float, double) is unable to represent exact decimal fractions and requires some degree of approximation making it unsuitable for monetary arithmetic. This constraint is an important consideration when modeling monetary data.
There are several approaches to modeling monetary data in MongoDB using the numeric and non-numeric models.
Numeric Model
The numeric model may be appropriate if you need to query the database for exact, mathematically valid matches or need to perform server-side arithmetic, e.g., $inc, $mul, and aggregation framework arithmetic.
The following approaches follow the numeric model:
- Using the Decimal BSON Type which is a decimal-based floating-point format capable of providing exact precision. Available in MongoDB version 3.4 and later.
- Using a Scale Factor to convert the monetary value to a 64-bit integer (
longBSON type) by multiplying by a power of 10 scale factor.
Non-Numeric Model
If there is no need to perform server-side arithmetic on monetary data or if server-side approximations are sufficient, modeling monetary data using the non-numeric model may be suitable.
The following approach follows the non-numeric model:
- Using two fields for the monetary value: One field stores the exact monetary value as a non-numeric
stringand another field stores a binary-based floating-point (doubleBSON type) approximation of the value.
Numeric Model
Using the Decimal BSON Type
New in version 3.4.
The decimal BSON type uses the IEEE 754 decimal128 decimal-based floating-point numbering format. Unlike binary-based floating-point formats (i.e., the double BSON type), decimal128 does not approximate decimal values and is able to provide the exact precision required for working with monetary data.
From the mongo shell decimal values are assigned and queried using the NumberDecimal() constructor. The following example adds a document containing gas prices to a gasprices collection:
db.gasprices.insert{ "_id" : 1, "date" : ISODate(), "price" : NumberDecimal("2.099"), "station" : "Quikstop", "grade" : "regular" }
The following query matches the document above:
db.gasprices.find( { price: NumberDecimal("2.099") } )
For more information on the decimal type, see NumberDecimal.
Converting Values to Decimal
A collection’s values can be transformed to the decimal type by performing a one-time transformation or by modifying application logic to perform the transformation as it accesses records.
One-Time Collection Transformation
A collection can be transformed by iterating over all documents in the collection, converting the monetary value to the decimal type, and writing the document back to the collection.
Note
It is strongly advised to add the decimal value to the document as a new field and remove the old field later once the new field’s values have been verified.
Warning
Be sure to test decimal conversions in an isolated test environment. Once datafiles are created or modified with MongoDB version 3.4 they will no longer be compatible with previous versions and there is no support for downgrading datafiles containing decimals.
Scale Factor Transformation:
Consider the following collection which used the Scale Factor approach and saved the monetary value as a 64-bit integer representing the number of cents:
{ "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : NumberLong("1999") },
{ "_id" : 2, "description" : "Jeans", "size" : "36", "price" : NumberLong("3999") },
{ "_id" : 3, "description" : "Shorts", "size" : "32", "price" : NumberLong("2999") },
{ "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : NumberLong("2495") },
{ "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : NumberLong("8000") }
The long value can be converted to an appropriately formatted decimal value by multiplying price and NumberDecimal("0.01") using the $multiply operator. The following aggregation pipeline assigns the converted value to the new priceDec field in the $addFields stage:
db.clothes.aggregate(
[
{ $match: { price: { $type: "long" }, priceDec: { $exists: 0 } } },
{
$addFields: {
priceDec: {
$multiply: [ "$price", NumberDecimal( "0.01" ) ]
}
}
}
]
).forEach( ( function( doc ) {
db.clothes.save( doc );
} ) )
The results of the aggregation pipeline can be verified using the db.clothes.find() query:
{ "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : NumberLong(1999), "priceDec" : NumberDecimal("19.99") }
{ "_id" : 2, "description" : "Jeans", "size" : "36", "price" : NumberLong(3999), "priceDec" : NumberDecimal("39.99") }
{ "_id" : 3, "description" : "Shorts", "size" : "32", "price" : NumberLong(2999), "priceDec" : NumberDecimal("29.99") }
{ "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : NumberLong(2495), "priceDec" : NumberDecimal("24.95") }
{ "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : NumberLong(8000), "priceDec" : NumberDecimal("80.00") }
If you do not want to add a new field with the decimal value, the original field can be overwritten. The following update() method first checks that price exists and that it is a long, then transforms the long value to decimal and stores it in the price field:
db.clothes.update(
{ price: { $type: "long" } },
{ $mul: { price: NumberDecimal( "0.01" ) } },
{ multi: 1 }
)
The results can be verified using the db.clothes.find() query:
{ "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : NumberDecimal("19.99") }
{ "_id" : 2, "description" : "Jeans", "size" : "36", "price" : NumberDecimal("39.99") }
{ "_id" : 3, "description" : "Shorts", "size" : "32", "price" : NumberDecimal("29.99") }
{ "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : NumberDecimal("24.95") }
{ "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : NumberDecimal("80.00") }