How to round and format numbers in MongoDB (with examples)

Introduction

MongoDB supports various number types including int, long, double, and decimal. It’s important to pick the correct type to ensure precision in calculations, which can affect the results when rounding numbers.

When working with number data types in MongoDB, it is often necessary to round and format the results for better presentation or further computation. MongoDB provides a variety of functions that you can use as part of its aggregation framework to achieve this. This comprehensive tutorial will guide you through how to round and format numbers in MongoDB, suitable for beginners and advanced users alike. Follow these examples, and you’ll be formatting numbers in no time.

Basic Rounding with $round

The $round aggregation pipeline stage rounds a number to the nearest integer or specified decimal place. Here is a basic example:

db.collection.aggregate([
  { $project: {
      roundedValue: { $round: ["$field"] }
  }}
])

Output:

{ "roundedValue": 25 }

Specifying Decimal Places

To round to a specific number of decimal places, simply supply the desired number of places as the second argument of the $round expression:

db.collection.aggregate([
  { $project: {
      roundedValue: { $round: ["$field", 2] }
  }}
])

Output:

{ "roundedValue": 25.13 }

Truncating Numbers with $trunc

The $trunc operator works similarly to $round but truncates the number to the integer or specific decimal place without rounding:

db.collection.aggregate([
  { $project: {
      truncatedValue: { $trunc: ["$field", 2] }
  }}
])

Output:

{ "truncatedValue": 25.12 }

Using $ceil and $floor

To always round up, you’ll use $ceil, and to always round down, you’ll use $floor.

db.collection.aggregate([
  { $project: {
      ceilingValue: { $ceil: "$field" },
      floorValue: { $floor: "$field" }
  }}
])

Output:

{ "ceilingValue": 26, "floorValue": 25 }

Advanced Rounding with $toDecimal

If you’re dealing with financial data or where precision is paramount, you might convert numbers to the $decimal type before performing any rounding.

db.collection.aggregate([
  { $project: {
      roundedDecimal: {
        $round: [{ $toDecimal: "$field" }, 2]
      }
  }}
])

Output:

{ "roundedDecimal": "25.13" }

Formatting Numbers for Output

Beside rounding, to display numbers in a more readable format or with currency symbols, you may convert and concatenate strings using MongoDB’s $concat along with $toString operator:

db.collection.aggregate([
  { $project: {
      formattedValue: { $concat: ["$", { $toString: { $round: ["$field", 2] }}] }
  }}
])

Output:

{ "formattedValue": "$25.13" }

Complex Aggregations Handling Rounding

In cases where you need to apply rounding within more complex aggregations, you can embed the rounding operators within calculations:

db.collection.aggregate([
  {
    $group: {
      _id: null,
      total: { $sum: "$amount" }
    }
  },
  {
    $project: {
      roundedTotal: { $round: ["$total", 2] }
    }
  }
])

This would give you an aggregation of sum values that are then rounded to two decimal places.

Dealing with Null or Missing Fields

What if the field you are attempting to round is null or doesn’t exist? It’s always good practice to handle such cases:

db.collection.aggregate([
  { $project: {
      roundedValue: {
        $cond: {
          if: { $eq: ["$field", null] },
          then: 0,
          else: { $round: ["$field"] }
        }
      }
  }}
])

The output will be 0 if the input is null or missing.

Notes

Type Conversion and Rounding

Note: With the introduction of version >=3.4 MongoDB, implict type conversion is considered when rounding numbers. Be mindful of this behavior when dealing with varied data types.

Error Handling and Validation

MongoDB queries don’t cover only successful cases; they should also handle potential exceptions and wrong value types. Considering proper type validation and error management within your queries will bolster their reliability in production scenarios.

Reviewing Performance Impact

It’s worth noting that rounding and formatting operations could have a performance impact on your database, especially with large collections. As with any database operation, make sure to monitor and optimize performance when needed.

Conclusion

In conclusion, formatting and rounding numbers in MongoDB is a common task that can easily be managed with the proper use of the aggregation pipeline. By understanding the various operators MongoDB offers, such as $round, $ceil, $floor, and $trunc, and by handling edge cases judiciously, your database queries will be accurate and efficient.