MongoDB $toDecimal

From MongoDB 4.0, you can use the $toDecimal aggregation pipeline operator to convert a value to a decimal.

Most types can be can be converted to decimal, but the ObjectId can’t.

When you convert a Date value to a decimal, $toDecimal returns the number of milliseconds since the epoch that corresponds to the date value.

When you convert a boolean to a decimal, if the boolean is true, then the decimal is 1. If the boolean is false, then the decimal is 0.

Example

Suppose we have a collection called types and it contains the following documents:

{
	"_id" : ObjectId("601340eac8eb4369cf6ad9db"),
	"double" : 123.75,
	"string" : "123",
	"boolean" : true,
	"date" : ISODate("2020-12-31T23:30:15.123Z"),
	"integer" : 123,
	"long" : NumberLong(123),
	"decimal" : NumberDecimal("123.75")
}

We can use the $toDecimal operator to convert those types (except for the ObjectId) to a decimal. If the input is already a decimal, then it simply returns the decimal.

db.types.aggregate(
  [
    {
      $project:
        { 
          _id: 0,
          double: { $toDecimal: "$double" },
          string: { $toDecimal: "$string" },
          boolean: { $toDecimal: "$boolean" },
          date: { $toDecimal: "$date" },
          integer: { $toDecimal: "$integer" },
          long: { $toDecimal: "$long" },
          decimal: { $toDecimal: "$decimal" }
        }
    }
  ]
).pretty()

Result:

{
	"double" : NumberDecimal("123.750000000000"),
	"string" : NumberDecimal("123"),
	"boolean" : NumberDecimal("1"),
	"date" : NumberDecimal("1609457415123"),
	"integer" : NumberDecimal("123.000000000000"),
	"long" : NumberDecimal("123"),
	"decimal" : NumberDecimal("123.75")
}

Errors

If you encounter errors, try using the $convert operator instead of $toDecimal. The $convert operator allows you to handle errors without affecting the whole aggregation operation.

The $toDecimal operator is the equivalent of using the $convert operator to convert a value to a decimal.

Here’s an example of using $convert to try to convert an ObjectId to a decimal (which results in an error):

db.types.aggregate(
  [
    {
      $project:
        { 
          _id: 0,
          result: 
          {
            $convert: { 
              input: "$_id", 
              to: "decimal",
              onError: "An error occurred",
              onNull: "Input was null or empty" 
            }
          }
        }
    }
  ]
)

Result:

{ "result" : "An error occurred" } 

Using $convert allowed us to specify the error message to use when the error occurred, and it didn’t halt the whole aggregation operation.

See MongoDB $convert for more examples.