how to convert string to numerical values in mongo

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

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

Answer 2

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

Answer 3

Collation is what you need:

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

Answer 4

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

Answer 5

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().

Answer 6

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.