how to convert string to numerical values in mongodb

Question

I am trying to convert a string that contains a numerical value to its value in an aggregate query in MongoDB   Example of document      id   ObjectId  5522XXXXXXXXXXXX        Date    2015-04-05       PartnerID    123456       moop    1234       Example of the aggregate query I use        aggregate   my collection       pipeline               match                 Date                        gt  2015-04-01                       lt   2015-04-05                                                  group                 id    PartnerID                 total   sum   moop                       where the results are         result                     id    123456           total   NumberInt 0             How can you convert the string to its numerical value

User · Answer

Here is a pure MongoDB based solution for this problem which I just wrote for fun. It's effectively a server-side string-to-number parser which supports positive and negative numbers as well as decimals:

db.collection.aggregate({
    $addFields: {
        "moop": {
            $reduce: {
                "input": {
                    $map: { // split string into char array so we can loop over individual characters
                        "input": {
                            $range: [ 0, { $strLenCP: "$moop" } ] // using an array of all numbers from 0 to the length of the string
                        },
                        "in":{
                            $substrCP: [ "$moop", "$$this", 1 ] // return the nth character as the mapped value for the current index
                        }
                    }
                },
                "initialValue": { // initialize the parser with a 0 value
                    "n": 0, // the current number
                    "sign": 1, // used for positive/negative numbers
                    "div": null, // used for shifting on the right side of the decimal separator "."
                    "mult": 10 // used for shifting on the left side of the decimal separator "."
                }, // start with a zero
                "in": {
                    $let: {
                        "vars": {
                            "n": {
                                $switch: { // char-to-number mapping
                                    branches: [
                                        { "case": { $eq: [ "$$this", "1" ] }, "then": 1 },
                                        { "case": { $eq: [ "$$this", "2" ] }, "then": 2 },
                                        { "case": { $eq: [ "$$this", "3" ] }, "then": 3 },
                                        { "case": { $eq: [ "$$this", "4" ] }, "then": 4 },
                                        { "case": { $eq: [ "$$this", "5" ] }, "then": 5 },
                                        { "case": { $eq: [ "$$this", "6" ] }, "then": 6 },
                                        { "case": { $eq: [ "$$this", "7" ] }, "then": 7 },
                                        { "case": { $eq: [ "$$this", "8" ] }, "then": 8 },
                                        { "case": { $eq: [ "$$this", "9" ] }, "then": 9 },
                                        { "case": { $eq: [ "$$this", "0" ] }, "then": 0 },
                                        { "case": { $and: [ { $eq: [ "$$this", "-" ] }, { $eq: [ "$$value.n", 0 ] } ] }, "then": "-" }, // we allow a minus sign at the start
                                        { "case": { $eq: [ "$$this", "." ] }, "then": "." }
                                    ],
                                    default: null // marker to skip the current character
                                } 
                            }
                        },
                        "in": {
                            $switch: {
                                "branches": [
                                    {
                                        "case": { $eq: [ "$$n", "-" ] },
                                        "then": { // handle negative numbers
                                            "sign": -1, // set sign to -1, the rest stays untouched
                                            "n": "$$value.n",
                                            "div": "$$value.div",
                                            "mult": "$$value.mult",
                                        },
                                    },
                                    {
                                        "case": { $eq: [ "$$n", null ] }, // null is the "ignore this character" marker
                                        "then": "$$value" // no change to current value
                                    }, 
                                    {
                                        "case": { $eq: [ "$$n", "." ] },
                                        "then": { // handle decimals
                                            "n": "$$value.n",
                                            "sign": "$$value.sign",
                                            "div": 10, // from the decimal separator "." onwards, we start dividing new numbers by some divisor which starts at 10 initially
                                            "mult": 1, // and we stop multiplying the current value by ten
                                        },
                                    }, 
                                ],
                                "default": {
                                    "n": {
                                        $add: [
                                            { $multiply: [ "$$value.n", "$$value.mult" ] }, // multiply the already parsed number by 10 because we're moving one step to the right or by one once we're hitting the decimals section
                                            { $divide: [ "$$n", { $ifNull: [ "$$value.div", 1 ] } ] } // add the respective numerical value of what we look at currently, potentially divided by a divisor
                                        ]
                                    },
                                    "sign": "$$value.sign",
                                    "div": { $multiply: [ "$$value.div" , 10 ] },
                                    "mult": "$$value.mult"
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}, {
    $addFields: { // fix sign
        "moop": { $multiply: [ "$moop.n", "$moop.sign" ] }
    }
})

I am certainly not advertising this as the bee's knees or anything and it might have severe performance implications for larger datasets over a client based solutions but there might be cases where it comes in handy...

The above pipeline will transform the following documents:

{ "moop": "12345" } --> { "moop": 12345 }

and

{ "moop": "123.45" } --> { "moop": 123.45 }

and

{ "moop": "-123.45" } --> { "moop": -123.45 }

and

{ "moop": "2018-01-03" } --> { "moop": 20180103.0 }

User · Answer

Three things need to care for    parseInt   will store double data type in mongodb  Please use new NumberInt string   in Mongo shell command for bulk usage  yield won t work  Please DO NOT add  yield   If you already change string to double by parseInt    It looks like you have no way to change the type to int directly  The solution is a little bit wired  change double to string first and then change back to int by new NumberInt

User · Answer

Using MongoDB 4 0 and newer  You have two options i e   toInt or  convert  Using  toInt  follow the example below   filterDateStage           match              Date                   gt    2015-04-01                  lt    2015-04-05                      groupStage           group               id     PartnerID            total       sum       toInt     moop                db getCollection  my collection   aggregate      filterDateStage     groupStage      If the conversion operation encounters an error  the aggregation operation stops and throws an error  To override this behavior  use  convert instead   Using  convert  groupStage           group               id     PartnerID            total                    sum                        convert      input     moop    to    int                                          Using Map Reduce  With map reduce you can use javascript functions like parseInt   to do the conversion  As an example  you could define the map function to process each input document  In the function  this refers to the document that the map-reduce operation is processing  The function maps the converted moop string value to the PartnerID for each document and emits the PartnerID and converted moop pair  This is where the javascript native function parseInt   can be applied   var mapper   function          var x   parseInt this moop       emit this PartnerID  x        Next  define the corresponding reduce function with two arguments keyCustId and valuesMoop  valuesMoop is an array whose elements are the integer moop values emitted by the map function and grouped by keyPartnerID  The function reduces the valuesMoop array to the sum of its elements   var reducer   function keyPartnerID  valuesMoop                      return Array sum valuesMoop                     db collection mapReduce      mapper      reducer                out    example results           query                 Date                     gt   2015-04-01                     lt   2015-04-05                                             db example results find function  err  docs        if err  console log err       console log JSON stringify docs           For example  with the following sample collection of documents      0            id    ObjectId  550c00f81bcc15211016699b         Date     2015-04-04        PartnerID     123456        moop     1234        1            id    ObjectId  550c00f81bcc15211016699c         Date     2015-04-03        PartnerID     123456        moop     24        2            id    ObjectId  550c00f81bcc15211016699d         Date     2015-04-02        PartnerID     123457        moop     21        3            id    ObjectId  550c00f81bcc15211016699e         Date     2015-04-02        PartnerID     123457        moop     8      The above Map Reduce operation will save the results to the example results collection and the shell command db example results find   will give      0            id     123456        value    1258       1            id     123457        value    29

User · Answer

If you can edit all documents in aggregate     TimeStamp     toDecimal    toDate    Your Date      And for the client  you set the query    Date parse  Your date  toISOString      That s what makes you whole work with ISODate

User · Answer

Eventually I used   db my collection find  moop    exists  true    forEach function obj        obj moop   new NumberInt obj moop       db my collection save obj         to turn moop from string to integer in my collection following the example in Simone s answer MongoDB  How to change the type of a field

User · Answer

Collation is what you need   db collectionName find   sort  PartnerID  1   collation  locale   en US   numericOrdering  true

User · Answer

It should be saved  It should be like this         db  my collection find     forEach function theCollection             theCollection moop   parseInt theCollection moop           db my collection save theCollection

User · Answer

You can easily convert the string data type to numerical data type   Don t forget to change collectionName  amp  FieldName  for ex   CollectionNmae   Users  amp  FieldName   Contactno   Try this query    db collectionName find   forEach  function  x    x FieldName   parseInt x FieldName   db collectionName save x

User · Answer

Try    TimeStamp    toDecimal     toDate   Datum

User · Answer

String can be converted to numbers in MongoDB v4 0 using  toInt operator  In this case  db col aggregate                  project                 id  0              moopNumber     toInt    moop                         outputs      moopNumber    1234

User · Answer

Though  toInt is really useful  it was added on mongoDB 4 0  I ve run into this same situation in a database running 3 2 which upgrading to use  toInt was not an option due to some other application incompatibilities  so i had to come up with something else  and actually was surprisingly simple   If you  project and  add zero to your string  it will turn into a number       project        convertedField       add      stringField  0         more fields here

User · Answer

MongoDB aggregation not allowed to change existing data type of given fields  In this case you should create some programming code to convert string to int  Check below code   db collectionName find   forEach function data        db collectionName update             id   data  id           moop   data moop                    set                  PartnerID   parseInt data PartnerID                         If your collections size more then above script will slow down the performance  for perfomace mongo provide mongo bulk operations  using mongo bulk operations also updated data type  var bulk   db collectionName initializeOrderedBulkOp    var counter   0  db collectionName find   forEach function data        var updoc               set                 var myKey    PartnerID       updoc   set   myKey    parseInt data PartnerID          queue the update     bulk find             id   data  id        update updoc       counter           Drain and re-initialize every 1000 update statements     if  counter   1000    0            bulk execute            bulk   db collectionName initializeOrderedBulkOp                        Add the rest in the queue if  counter   1000    0  bulk execute      This basically reduces the amount of operations statements sent to the sever to only sending once every 1000 queued operations

User · Answer

db user find   toArray   filter a  a age 40

[mongodb] how to convert string to numerical values in mongodb

Examples related to mongodb

Examples related to mongodb-query