In MongoDB, you can use the $convert aggregation pipeline operator to convert a value to a specified type.
You can convert any valid expression to a double, string, ObjectId, boolean, Date, integer, long, or decimal.
Not all types can be converted to any other type. Some types can only be converted from a subset of the available MongoDB types. For example, you can’t convert a date to an integer.
You can optionally use the onError parameter to specify what to return in the event of an error. You can optionally use the onNull parameter to specify what to return if the input value is null or missing.
Sample Data
Suppose we have a collection called samples with the following document:
{
"_id" : ObjectId("6011e471c8eb4369cf6ad9d5"),
"double" : 123.75,
"string" : "123",
"boolean" : true,
"date" : ISODate("2020-12-31T23:30:15.123Z"),
"integer" : 123,
"long" : NumberLong(123),
"decimal" : NumberDecimal("123.75"),
"datestring" : "2021-02-15 06:53:55"
}
The following examples demonstrate how to convert each field to other types.
Convert ObjectId to String
The _id field in the above document is an ObjectId. Here’s an example of converting the ObjectId to a string.
db.samples.aggregate(
[
{
$project:
{
result:
{
$convert: {
input: "$_id",
to: "string",
onError: "An error occurred",
onNull: "Input was null or empty"
}
}
}
}
]
).pretty()Result:
{
"_id" : ObjectId("6011e471c8eb4369cf6ad9d5"),
"result" : "6011e471c8eb4369cf6ad9d5"
}
The result is that the hexadecimal string from the ObjectId is returned as a string.
Convert Double to Integer
When you convert a double to an integer, the truncated value is returned.
db.samples.aggregate(
[
{
$project:
{
_id: 0,
result:
{
$convert: {
input: "$double",
to: "int",
onError: "An error occurred",
onNull: "Input was null or empty"
}
}
}
}
]
)Result:
{ "result" : 123 }
The truncated double value must fall within the minimum and maximum value for an integer. If it doesn’t an error will occur.
Also, you cannot convert a double value whose truncated value is less than the minimum integer value or is greater than the maximum integer value.
Convert String to Integer
When you convert a string to an integer, $convert returns the numerical value of the string as an integer.
db.samples.aggregate(
[
{
$project:
{
_id: 0,
result:
{
$convert: {
input: "$string",
to: "int",
onError: "An error occurred",
onNull: "Input was null or empty"
}
}
}
}
]
)Result:
{ "result" : 123 }
The string value must be a base10 integer (e.g. "-123", "123") and fall within the minimum and maximum value for an integer.
Convert Boolean to Integer
When you convert a boolean to an integer, $convert returns 1 for a boolean value of true, and 0 for a boolean value of false.
db.samples.aggregate(
[
{
$project:
{
_id: 0,
result:
{
$convert: {
input: "$boolean",
to: "int",
onError: "An error occurred",
onNull: "Input was null or empty"
}
}
}
}
]
)Result:
{ "result" : 1 }
Convert Date to String
You can use $convert to return a date as a string.
db.samples.aggregate(
[
{
$project:
{
_id: 0,
result:
{
$convert: {
input: "$date",
to: "string",
onError: "An error occurred",
onNull: "Input was null or empty"
}
}
}
}
]
)Result:
{ "result" : "2020-12-31T23:30:15.123Z" }
The Date object has been converted to a string.
Convert Double to Date
The following types can be converted to a date:
- double
- decimal
- long
- string
- ObjectId
Here’s an example of converting a double to a date:
db.samples.aggregate(
[
{
$project:
{
_id: 0,
result:
{
$convert: {
input: "$double",
to: "date",
onError: "An error occurred",
onNull: "Input was null or empty"
}
}
}
}
]
)Result:
{ "result" : ISODate("1970-01-01T00:00:00.123Z") }
When you convert numbers to a date, the number represents the number of milliseconds since January 1, 1970.
In our example, we provided a double value of 123, which was interpreted as 123 milliseconds since January 1, 1970.
Convert Integer to Decimal
Here’s an example of converting an integer to decimal:
db.samples.aggregate(
[
{
$project:
{
_id: 0,
result:
{
$convert: {
input: "$integer",
to: "decimal",
onError: "An error occurred",
onNull: "Input was null or empty"
}
}
}
}
]
)Result:
{ "result" : NumberDecimal("123.000000000000") }
Convert String to Date
Here’s an example of converting a date/time string to a Date object:
db.samples.aggregate(
[
{
$project:
{
_id: 0,
result:
{
$convert: {
input: "$datestring",
to: "date",
onError: "An error occurred",
onNull: "Input was null or empty"
}
}
}
}
]
)Result:
{ "result" : ISODate("2021-02-15T06:53:55Z") }
When you convert a string to a Date object, the string must be a valid date string, such as:
- 2021-02-15
- 2021-02-15T06:53:55
- 2021-02-15T06:53:55Z
Convert to Boolean
When you convert a value to a boolean, the result will be true or false, depending on the input value.
Generally, for numeric values, this will return false if the value is zero (0), and true for any other value.
For string, ObjectId, and Date values, it will always return true.
db.samples.aggregate(
[
{
$project:
{
_id: 0,
result:
{
$convert: {
input: "$string",
to: "bool",
onError: "An error occurred",
onNull: "Input was null or empty"
}
}
}
}
]
)Result:
{ "result" : true }
The onError Parameter
You can use the onError parameter to return a specified value in the event that an error occurs.
Suppose we have a collection called dogs with the following document:
{ "_id" : 1, "name" : "Wag", "born" : "March 2020", "weight" : null }
Below is an example of trying to do a conversion that fails due to an error. In the first example, we don’t use onError.
db.dogs.aggregate(
[
{
$project:
{
_id: 0,
result:
{
$convert: {
input: "$born",
to: "int"
}
}
}
}
]
)Result:
Error: command failed: {
"ok" : 0,
"errmsg" : "Failed to parse number 'March 2020' in $convert with no onError value: Did not consume whole string.",
"code" : 241,
"codeName" : "ConversionFailure"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:618:17
assert.commandWorked@src/mongo/shell/assert.js:708:16
DB.prototype._runAggregate@src/mongo/shell/db.js:266:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1046:12
@(shell):1:1That resulted in a nasty error message being displayed.
The following example shows how we can make this nicer by using the onError parameter.
db.dogs.aggregate(
[
{
$project:
{
_id: 0,
result:
{
$convert: {
input: "$born",
to: "int",
onError: "An error occurred",
onNull: "Input was null or empty"
}
}
}
}
]
)Result:
{ "result" : "An error occurred" }
Notice that we still get the document returned, and our custom error message is displayed in the field.
The onNull Parameter
You can optionally use the onNull parameter to specify what to return if the input value is null or missing.
Using the previous document, we can test the onNull parameter like this:
db.dogs.aggregate(
[
{
$project:
{
_id: 0,
result:
{
$convert: {
input: "$weight",
to: "decimal",
onError: "An error occurred",
onNull: "Input was null or empty"
}
}
}
}
]
)Result:
{ "result" : "Input was null or empty" }