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?

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