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.

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