MongoDB: Converting string to number (with examples)

Introduction

In the world of databases, data types are foundational. They define how data is stored, processed, and validated. MongoDB, a widely-used NoSQL database, allows for flexible schema design, which means that sometimes data types might need to be transformed post-collection. Converting strings to numbers is a common requirement in Mongo operations, particularly when dealing with analytical tasks or comparison operations where you want numerical efficiency and correctness.

This post will guide you through various methods of converting strings to numbers in MongoDB, complete with code examples ranging from basic to more advanced use-cases.

Understanding the Data Type Challenge

MongoDB stores data in BSON format, which supports a vast array of data types such as string, integer, decimal, object, array, and more. When data is imported from various sources, particularly from CSV or JSON, numbers may often be interpreted as strings. As such, ensuring that numerical values are stored or manipulated as numbers is essential for efficiency and correctness.

Basic Conversion Techniques

Let’s start with the basic level where we intend to convert a known string field to a number.

Using $toInt and $toDecimal

db.collection.find({}).forEach(function(doc) {
  doc.myNumberField = parseInt(doc.myNumberField);
  db.collection.save(doc);
});

In this example, we fetch every document from a given collection, parse the field ‘myNumberField’ into an integer using JavaScript’s parseInt function, and save the document back to the collection. This is a simple approach, but it is not optimized for performance on large datasets.

For better performance and more concise code, MongoDB’s aggregation framework provides the $toInt and $toDecimal operators which can be used as follows:

db.collection.aggregate([
  {
    $set: {
      myNumberField: { $toInt: "$myNumberField" }
    }
  }
])

Here, we are using the aggregation pipeline with the $set stage to convert the field ‘myNumberField’ to an integer.

If you want to convert to a decimal instead, you can use $toDecimal:

db.collection.aggregate([
  {
    $set: {
      myNumberField: { $toDecimal: "$myNumberField" }
    }
  }
])

It’s important to note that $toInt and $toDecimal will return an error if the string doesn’t contain a number, or contains additional characters that are not part of a valid numeric format.

Handling Bulk Conversions

When dealing with a large number of documents or more complex requirements, you might want to use a bulk operation. This allows you to manipulate multiple documents in batches, reducing the number of write operations. An example would look like this:

const bulk = db.collection.initializeUnorderedBulkOp();
db.collection.find({}).forEach(function(doc) {
  bulk.find({_id: doc._id}).updateOne({
    $set: { "myNumberField": parseInt(doc.myNumberField) ? Number(doc.myNumberField) : null }
  });
});
bulk.execute();

In this script, we initialize a bulk operation, iterate over each document, and attempt to convert ‘myNumberField’ to a number using the JavaScript Number function, defaulting to null if the conversion isn’t possible.

The bulk.execute() method sends all collected operations to the MongoDB server in a single batch.

Error Handling in Conversions

While converting data types, there’s always the risk of encountering values that cannot be neatly cast to numbers.

Using $convert

$convert is an aggregation operator that allows for error handling. Here’s an example of how to use it:

db.collection.aggregate([
  {
    $set: {
      myNumberField: {
        $convert: {
          input: "$myNumberField",
          to: "int",
          onError: "Conversion error",
          onNull: "Field is null"
        }
      }
    }
  }
])

This operation will attempt to convert ‘myNumberField’ to an integer and, in cases where the conversion fails, it will set the field to ‘Conversion error’. If the field is null, it will return ‘Field is null’.

Advanced Use Cases

In more advanced scenarios, you might encounter collections where the type of data in a field varies and may require conditional conversion. You can deal with such scenarios using the aggregation framework’s conditional operators like $cond.

Conditional Type Conversion

db.collection.aggregate([
  {
    $set: {
      myNumberField: {
        $cond: {
          if: { $isNumber: "$myNumberField" },
          then: "$myNumberField",
          else: {
            $convert: {
              input: "$myNumberField",
              to: "double",
              onError: 0
            }
          }
        }
      }
    }
  }
])

In the above pipeline, we use $cond to check if ‘myNumberField’ is already a number. If it is, we leave it unchanged. Otherwise, we attempt to convert it to a double, defaulting to 0 in case of an error.

Conclusion

MongoDB provides several tools for converting string data to numeric types, which are vital for analyses and operations that require numeric precision. By following the techniques and patterns outlined in this tutorial, you’ll be equipped to ensure your data types align with your application’s logic and MongoDB’s performance strengths. Remember, conversion is not just about changing types—it’s about guaranteeing the integrity and utility of your data.