Model Monetary Data

Overview

Applications that handle monetary data often require the ability tocapture fractional units of currency and need to emulate decimalrounding 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 fractionsand requires some degree of approximation making it unsuitable formonetary arithmetic. This constraint is an important consideration whenmodeling monetary data.

There are several approaches to modeling monetary data in MongoDB usingthe numeric and non-numeric models.

Numeric Model

The numeric model may be appropriate if you need to query thedatabase for exact, mathematically valid matches or need to performserver-side arithmetic, e.g., $inc, $mul, andaggregation framework arithmetic.

The following approaches follow the numeric model:

  • Using the Decimal BSON Type which is a decimal-basedfloating-point format capable of providing exact precision. Availablein MongoDB version 3.4 and later.
  • Using a Scale Factor to convert the monetary valueto a 64-bit integer (long BSON type) by multiplying by a power of10 scale factor.

Non-Numeric Model

If there is no need to perform server-side arithmetic on monetary dataor if server-side approximations are sufficient, modeling monetary datausing 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 anon-numeric string and another field stores a binary-basedfloating-point (double BSON type) approximation of the value.

Note

Arithmetic mentioned on this page refers to server-sidearithmetic performed by mongod or mongos, andnot to client-side arithmetic.

Numeric Model

Using the Decimal BSON Type

New in version 3.4.

The decimal BSON type uses theIEEE 754 decimal128 decimal-based floating-point numbering format.Unlike binary-based floating-point formats (i.e., the double BSONtype), decimal128 does not approximate decimal values and is able toprovide the exact precision required for working with monetary data.

From the mongo shell decimal values are assigned andqueried using the NumberDecimal() constructor. The followingexample adds a document containing gas prices to a gaspricescollection:

  1. db.gasprices.insert{ "_id" : 1, "date" : ISODate(), "price" : NumberDecimal("2.099"), "station" : "Quikstop", "grade" : "regular" }

The following query matches the document above:

  1. db.gasprices.find( { price: NumberDecimal("2.099") } )

For more information on the decimal type, seeNumberDecimal.

Converting Values to Decimal

A collection’s values can be transformed to the decimal type byperforming a one-time transformation or by modifying application logicto perform the transformation as it accesses records.

Tip

Alternative to the procedure outlined below, starting in version4.0, you can use the $convert and its helper$toDecimal operator to convert values to NumberDecimal().

One-Time Collection Transformation

A collection can be transformed by iterating over all documents in thecollection, converting the monetary value to the decimal type, andwriting the document back to the collection.

Note

It is strongly advised to add the decimal value to thedocument as a new field and remove the old field later once thenew field’s values have been verified.

Warning

Be sure to test decimal conversions in anisolated test environment. Once datafiles are created or modifiedwith MongoDB version 3.4 they will no longer be compatible withprevious versions and there is no support for downgrading datafilescontaining decimals.

Scale Factor Transformation:

Consider the following collection which used theScale Factor approach and savedthe monetary value as a 64-bit integer representing the number of cents:

  1. { "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : NumberLong("1999") },
  2. { "_id" : 2, "description" : "Jeans", "size" : "36", "price" : NumberLong("3999") },
  3. { "_id" : 3, "description" : "Shorts", "size" : "32", "price" : NumberLong("2999") },
  4. { "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : NumberLong("2495") },
  5. { "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : NumberLong("8000") }

The long value can be converted to an appropriately formatteddecimal value by multiplying price andNumberDecimal("0.01") using the $multiply operator.The following aggregation pipeline assigns the converted value to thenew priceDec field in the $addFields stage:

  1. db.clothes.aggregate(
  2.   [
  3.     { $match: { price: { $type: "long" }, priceDec: { $exists: 0 } } },
  4.     {
  5.       $addFields: {
  6.         priceDec: {
  7.           $multiply: [ "$price", NumberDecimal( "0.01" ) ]
  8.         }
  9.       }
  10.     }
  11.   ]
  12. ).forEach( ( function( doc ) {
  13.   db.clothes.save( doc );
  14. } ) )

The results of the aggregation pipeline can be verified using thedb.clothes.find() query:

  1. { "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : NumberLong(1999), "priceDec" : NumberDecimal("19.99") }
  2. { "_id" : 2, "description" : "Jeans", "size" : "36", "price" : NumberLong(3999), "priceDec" : NumberDecimal("39.99") }
  3. { "_id" : 3, "description" : "Shorts", "size" : "32", "price" : NumberLong(2999), "priceDec" : NumberDecimal("29.99") }
  4. { "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : NumberLong(2495), "priceDec" : NumberDecimal("24.95") }
  5. { "_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, theoriginal field can be overwritten. The followingupdate() method first checks that priceexists and that it is a long, then transforms the long value todecimal and stores it in the price field:

  1. db.clothes.update(
  2.   { price: { $type: "long" } },
  3.   { $mul: { price: NumberDecimal( "0.01" ) } },
  4.   { multi: 1 }
  5. )

The results can be verified using the db.clothes.find() query:

  1. { "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : NumberDecimal("19.99") }
  2. { "_id" : 2, "description" : "Jeans", "size" : "36", "price" : NumberDecimal("39.99") }
  3. { "_id" : 3, "description" : "Shorts", "size" : "32", "price" : NumberDecimal("29.99") }
  4. { "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : NumberDecimal("24.95") }
  5. { "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : NumberDecimal("80.00") }

Non-Numeric Transformation:

Consider the following collection which used thenon-numericmodel and saved the monetary value as a string with the exactrepresentation of the value:

  1. { "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : "19.99" }
  2. { "_id" : 2, "description" : "Jeans", "size" : "36", "price" : "39.99" }
  3. { "_id" : 3, "description" : "Shorts", "size" : "32", "price" : "29.99" }
  4. { "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : "24.95" }
  5. { "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : "80.00" }

The following function first checks that price exists and that itis a string, then transforms the string value to a decimalvalue and stores it in the priceDec field:

  1. db.clothes.find( { $and : [ { price: { $exists: true } }, { price: { $type: "string" } } ] } ).forEach( function( doc ) {
  2.   doc.priceDec = NumberDecimal( doc.price );
  3.   db.clothes.save( doc );
  4. } );

The function does not output anything to the command line. The resultscan be verified using the db.clothes.find() query:

  1. { "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : "19.99", "priceDec" : NumberDecimal("19.99") }
  2. { "_id" : 2, "description" : "Jeans", "size" : "36", "price" : "39.99", "priceDec" : NumberDecimal("39.99") }
  3. { "_id" : 3, "description" : "Shorts", "size" : "32", "price" : "29.99", "priceDec" : NumberDecimal("29.99") }
  4. { "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : "24.95", "priceDec" : NumberDecimal("24.95") }
  5. { "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : "80.00", "priceDec" : NumberDecimal("80.00") }
Application Logic Transformation

It is possible to perform the transformation to the decimaltype from within the application logic. In this scenario theapplication modified to perform the transformation as it accessesrecords.

The typical application logic is as follows:

  • Test that the new field exists and that it is of decimal type
  • If the new decimal field does not exist:
    • Create it by properly converting old field values
    • Remove the old field
    • Persist the transformed record

Using a Scale Factor

Note

If you are using MongoDB version 3.4 or higher, using thedecimal type for modelingmonetary data is preferable to theScale Factor method.

To model monetary data using the scale factor approach:

  • Determine the maximum precision needed for the monetary value. Forexample, your application may require precision down to the tenth ofone cent for monetary values in USD currency.
  • Convert the monetary value into an integer by multiplying the valueby a power of 10 that ensures the maximum precision needed becomes theleast significant digit of the integer. For example, if the requiredmaximum precision is the tenth of one cent, multiply the monetary valueby 1000.
  • Store the converted monetary value.For example, the following scales 9.99 USD by 1000 to preserveprecision up to one tenth of a cent.
  1. { price: 9990, currency: "USD" }

The model assumes that for a given currency value:

  • The scale factor is consistent for a currency; i.e. same scalingfactor for a given currency.
  • The scale factor is a constant and known property of the currency;i.e applications can determine the scale factor from the currency.

When using this model, applications must be consistent in performingthe appropriate scaling of the values.

For use cases of this model, see Numeric Model.

Non-Numeric Model

To model monetary data using the non-numeric model, store thevalue in two fields:

  • In one field, encode the exact monetary value as a non-numeric datatype; e.g., BinData or a string.
  • In the second field, store a double-precision floating pointapproximation of the exact value.The following example uses the non-numeric model to store9.99 USD for the price and 0.25 USD for the fee:
  1. {
  2.   price: { display: "9.99", approx: 9.9900000000000002, currency: "USD" },
  3.   fee: { display: "0.25", approx: 0.2499999999999999, currency: "USD" }
  4. }

With some care, applications can perform range and sort queries on thefield with the numeric approximation. However, the use of the approximationfield for the query and sort operations requires that applicationsperform client-side post-processing to decode the non-numericrepresentation of the exact value and then filter out the returneddocuments based on the exact monetary value.

For use cases of this model, seeNon-Numeric Model.