how to convert string to numerical values in mongodb

Solution 1:

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.

Solution 2:

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

Solution 3:

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
}

Solution 4:

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