CODEWITHSUNDEEP
mongodbmongodb-query
Naftsen
Naftsen

Reputation: 871

how to convert string to numerical values in mongodb

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?

Upvotes: 66

Views: 168784

Answers (13)

DanielMK
DanielMK

Reputation: 601

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

Upvotes: 2

Dalibor Adamec
Dalibor Adamec

Reputation: 71

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.

Upvotes: 3

Dalibor Adamec
Dalibor Adamec

Reputation: 71

Try:

"TimeStamp":{$toDecimal: { $toDate:"$Datum"}}

Upvotes: 2

Naftsen
Naftsen

Reputation: 871

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?.

Upvotes: 21

Amarendra Kumar
Amarendra Kumar

Reputation: 868

You can easily convert the string data type to numerical data type.

Don't forget to change collectionName & FieldName. for ex : CollectionNmae : Users & FieldName : Contactno.

Try this query..

db.collectionName.find().forEach( function (x) {
x.FieldName = parseInt(x.FieldName);
db.collectionName.save(x);
});

Upvotes: 29

dnickless
dnickless

Reputation: 10918

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 }

Upvotes: 11

chridam
chridam

Reputation: 103465

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
}

Upvotes: 31

Lakshmanan Murugappan
Lakshmanan Murugappan

Reputation: 21

db.user.find().toArray().filter(a=>a.age>40)

Upvotes: -2

mickl
mickl

Reputation: 49985

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 }

Upvotes: 15

Dan Loughney
Dan Loughney

Reputation: 4677

Collation is what you need:

db.collectionName.find().sort({PartnerID: 1}).collation({locale: "en_US", numericOrdering: true})

Upvotes: 1

Reza
Reza

Reputation: 3038

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);
     });

Upvotes: 2

Bing Wu
Bing Wu

Reputation: 437

Three things need to care for:

  1. parseInt() will store double data type in mongodb. Please use new NumberInt(string).
  2. in Mongo shell command for bulk usage, yield won't work. Please DO NOT add 'yield'.
  3. 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().

Upvotes: 7

Neo-coder
Neo-coder

Reputation: 7840

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.

Upvotes: 39

Related Questions