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

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

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

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

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    -- gt     moop   12345     and     moop    123 45    -- gt     moop   123 45     and     moop    -123 45    -- gt     moop   -123 45     and     moop    2018-01-03    -- gt     moop   20180103 0

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

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

db user find   toArray   filter a  a age 40

User · Answer

Try    TimeStamp    toDecimal     toDate   Datum

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

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

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

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

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

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

Examples related to mongodb

Examples related to mongodb-query