[SERVER-11400] Need a type conversion mechanism to convert between strings and numbers Created: 28/Oct/13  Updated: 06/Dec/22  Resolved: 06/Mar/18

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 2.5.3
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Asya Kamsky Assignee: Backlog - Query Team (Inactive)
Resolution: Duplicate Votes: 15
Labels: expression, usability
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Duplicate
duplicates SERVER-33510 Add shorthand syntax for $convert Closed
Related
is related to DRIVERS-297 Aggregation Framework Support for 3.4 Closed
is related to SERVER-23410 Improve or remove support for dates i... Closed
is related to SERVER-24947 Need a type conversion mechanism for ... Closed
Assigned Teams:
Query
Sprint: Query 12 (04/04/16), Query 13 (04/22/16), Query 15 (06/03/16), Query 17 (07/15/16)
Participants:
Case:

 Description   

Syntax

{$toInt: <arbitrary expression>}
{$toLong: <arbitrary expression>}
{$toDouble: <arbitrary expression>}
{$toDecimal: <arbitrary expression>}
{$toString: <arbitrary expression>}

Examples

> db.coll.insert([
  {_id: 0, x: "3.4"},
  {_id: 1, x: 0.99}
]);
> db.coll.aggregate([{
  $project: {
    intField: {$toInt: "$x"}
  }
}])
{_id: 0, intField: 3}
{_id: 1, intField: 0}
 
// Example 2
> db.coll.insert([
  {_id: 0, x: {a: "b"}},
  {_id: 1, x: 1.22},
  {_id: 2, x: "abc"}
]);
> db.coll.aggregate([{
  $project: {
    stringField: {$toString: "$x"}
  }
}])
{_id: 0, stringField: "{a: 'b'}"}
{_id: 1, stringField: "1.22"}
{_id: 2, stringField: "abc"}

Notes

  • Each numeric conversion can convert from any numeric type or from a string (truncating if necessary).
  • Calling $toInt on an int is a no-op, similarly for other conversions.

Errors

  • If the input is of a type or value that cannot be converted:
    $toInt, $toLong, $toDouble, $toDecimal accept only int, long, double, decimal, and strings that can be interpreted as the above. $toString accepts anything that can be turned into output that is parseable by the mongo shell.

Old Description
There are situations where I want to use a number as (part of) a string but $concat will only take strings and not numbers. And there are other times where I have a string of a number "2012" and I want to use it as a number and there is no operator to do this.

Seems painful to fall back to map/reduce solely because someone saves a number or date as a string



 Comments   
Comment by Charlie Swanson [ 06/Apr/18 ]

As a work around in 3.6, you could consider using a collation with numericOrdering: true for that particular issue. See the docs for collation

Hope that helps.

Comment by Ankit Rathore [ 05/Apr/18 ]

is there any work around for this in the current mongo version 3.6 , till this fix released. i am struggling with this issue . Any help will be much appreciated. i need to select converted integer value from a string in '$project' stage and i have to compare this value in '$match' stage for $gt and $lt comparison.

for example
db.employee.aggregate(
{$project: {empID:

{'$toInt' : '$id'}

, empName:'$name'},
{$match:{ empID:

{'$gt':100}

}}
)

Comment by Justin Seyster [ 06/Mar/18 ]

We now have $toInt, $toString, etc. conversion operators in aggregation (SERVER-33510). These operators are shorthand for the $convert operator (SERVER-32784), which additionally allows for a "default" value to use when conversions fail. Now that we have comprehensive type conversion support, I'm closing this ticket as a duplicate.

Comment by Charlie Swanson [ 05/Jul/16 ]

asya, my hunch is no. If you put a default that's not an integer we'd assume you know what you're doing? In particular, I believe adinoyi.omuya was considering using a default of null to allow distinguishing those that did not convert cleanly?

Comment by Asya Kamsky [ 02/Jul/16 ]

Will we error if default is not an integer? Like null for instance?

Comment by Eliot Horowitz (Inactive) [ 01/Jul/16 ]

I'm ok with the suggestion above.

Comment by Charlie Swanson [ 30/Jun/16 ]

adinoyi.omuya has pointed out that it would be useful to have an optional default specified, as well as coerceToFoo versions which are more lenient.

Something like the following:

// {$toInt: [value, default value if unable to convert]}
{$toInt: "xxx"}  // Error!
{$toInt: "42.4.4"}  // Error!
{$toInt: "42andsomestring"} // Error!
{$toInt: ["xxx", 0]} // 0
{$toInt: ["42.4.4", 0]} // 0
// {$coerceToInt: [value, default if unable to convert - defaults to 0]}
{$coerceToInt: "xxx"} // 0
{$coerceToInt: ["xxx", -1]} // -1
{$coerceToInt: "42.4.4"} // 42
{$coerceToInt: ["42.4.4", -1]} // 42
{$coerceToInt: "42andsomestring"} // 42

I can think of a couple alternatives worth exploring:

  1. Using named arguments, e.g. {$toInt: {value: <expression>, default: <expression>}}. This would allow for some sort of 'base' option, to parse the number 15 from "0xf"
  2. Having each always default to null or the "missing" value, so that you could use $ifNull to default yourself.
  3. Having no default option on the $toFoo expressions, and only having a default on the $coerceToFoo versions

That said, I think the proposal above (in the example code) is still the best way forward.

asya, david.storch, eliot, thoughts?

Comment by Matt Kangas [ 02/Jun/15 ]

Today at MongoDB World nuha requested "atoi" or similar functionality in the agg framework.

Comment by Mathias Stearn [ 17/Feb/15 ]

While that works, I think that is best considered an "unintentional feature" (ie a bug). Either way, I think it is separately worth adding a real, explicit way to stringify a number similar to $dateToString.

Comment by Asya Kamsky [ 17/Feb/15 ]

it turns out both 2.6 and 3.0 allow using $substr to convert a number to a string.

> db.convert.find()
{ "_id" : ObjectId("54e3c7cfe34be0605bb58bcd"), "a" : 123, "b" : 456, "c" : NumberLong(7890) }
> db.convert.aggregate({$project:{as:{$substr:["$a",0,999]}, bs:{$substr:["$b",0,999]}, cs:{$substr:["$c",0,999]}}})
{ "_id" : ObjectId("54e3c7cfe34be0605bb58bcd"), "as" : "123", "bs" : "456", "cs" : "7890" }

Generated at Thu Feb 08 03:25:42 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.