Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-11400

Need a type conversion mechanism to convert between strings and numbers

    Details

    • Sprint:
      Query 12 (04/04/16), Query 13 (04/22/16), Query 15 (06/03/16), Query 17 (07/15/16)

      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

        Issue Links

          Activity

          Hide
          asya Asya Kamsky added a comment -

          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" }

          Show
          asya Asya Kamsky added a comment - 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" }
          Hide
          redbeard0531 Mathias Stearn added a comment -

          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.

          Show
          redbeard0531 Mathias Stearn added a comment - 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.
          Hide
          matt.kangas Matt Kangas (Inactive) added a comment -

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

          Show
          matt.kangas Matt Kangas (Inactive) added a comment - Today at MongoDB World nuri halperin requested "atoi" or similar functionality in the agg framework.
          Hide
          charlie.swanson Charlie Swanson added a comment -

          Wisdom 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 Kamsky, David Storch, Eliot Horowitz, thoughts?

          Show
          charlie.swanson Charlie Swanson added a comment - Wisdom 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: 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" Having each always default to null or the "missing" value, so that you could use $ifNull to default yourself. 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 Kamsky , David Storch , Eliot Horowitz , thoughts?
          Hide
          eliot Eliot Horowitz added a comment -

          I'm ok with the suggestion above.

          Show
          eliot Eliot Horowitz added a comment - I'm ok with the suggestion above.
          Hide
          asya Asya Kamsky added a comment -

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

          Show
          asya Asya Kamsky added a comment - Will we error if default is not an integer? Like null for instance?
          Hide
          charlie.swanson Charlie Swanson added a comment -

          Asya Kamsky, 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 Wisdom Omuya was considering using a default of null to allow distinguishing those that did not convert cleanly?

          Show
          charlie.swanson Charlie Swanson added a comment - Asya Kamsky , 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 Wisdom Omuya was considering using a default of null to allow distinguishing those that did not convert cleanly?

            People

            • Votes:
              14 Vote for this issue
              Watchers:
              26 Start watching this issue

              Dates

              • Created:
                Updated:

                  Agile