MongoDB convert string type to float type

Question

Following the suggestions over here MongoDB: How to change the type of a field? I tried to update my collection to change the type of field and its value.

Here is the update query

db.MyCollection.find({"ProjectID" : 44, "Cost": {$exists: true}}).forEach(function(doc){
    if(doc.Cost.length > 0){
        var newCost = doc.Cost.replace(/,/g, '').replace(/\$/g, '');
        doc.Cost =  parseFloat(newCost).toFixed(2);  
        db.MyCollection.save(doc);
        } // End of If Condition
    }) // End of foreach

upon completion of the above query, when I run the following command

db.MyCollection.find({"ProjectID" : 44},{Cost:1})

I still have Cost field as string.

{
    "_id" : ObjectId("576919b66bab3bfcb9ff0915"),
    "Cost" : "11531.23"
}

/* 7 */
{
    "_id" : ObjectId("576919b66bab3bfcb9ff0916"),
    "Cost" : "13900.64"
}

/* 8 */
{
    "_id" : ObjectId("576919b66bab3bfcb9ff0917"),
    "Cost" : "15000.86"
}

What am I doing wrong here?

Here is the sample document

/* 2 */
{
    "_id" : ObjectId("576919b66bab3bfcb9ff0911"),
    "Cost" : "$7,100.00"
}

/* 3 */
{
    "_id" : ObjectId("576919b66bab3bfcb9ff0912"),
    "Cost" : "$14,500.00"
}

/* 4 */
{
    "_id" : ObjectId("576919b66bab3bfcb9ff0913"),
    "Cost" : "$12,619.00"
}

/* 5 */
{
    "_id" : ObjectId("576919b66bab3bfcb9ff0914"),
    "Cost" : "$9,250.00"
}

Solution 1

The problem is that toFixed returns an String, not a Number. Then your are just updating the document with a new, and different String.

Example from Mongo Shell:

> number = 2.3431
2.3431
> number.toFixed(2)
2.34
> typeof number.toFixed(2)
string

If you want a 2 decimals number you must parse it again with something like:

db.MyCollection.find({"ProjectID" : 44, "Cost": {$exists: true}}).forEach(function(doc){
  if(doc.Cost.length > 0){
    var newCost = doc.Cost.replace(/,/g, '').replace(/\$/g, '');
    var costString = parseFloat(newCost).toFixed(2);
    doc.Cost = parseFloat(costString);
    db.MyCollection.save(doc);
  } // End of If Condition
}) // End of foreach

Solution 2

Follow this pattern to convert a currency field of string type to a float. You need to query all the documents in the collection that have the Cost field type string. To do so you would need to take advantage of using the Bulk API for bulk updates. These offer better performance as you will be sending the operations to the server in batches of say 1000, which gives you a better performance as you are not sending every request to the server, but just once in every 1000 requests.

The following demonstrates this approach, the first example uses the Bulk API available in MongoDB versions >= 2.6 and < 3.2. It updates all the documents in the collection by changing all the Cost fields to floating value fields:

var bulk = db.MyCollection.initializeUnorderedBulkOp(),
    counter = 0;

db.MyCollection.find({ 
    "Cost": { "$exists": true, "$type": 2 } 
}).forEach(function (doc) {
    var newCost = Number(doc.Cost.replace(/[^0-9\.]+/g,"")); 
    bulk.find({ "_id": doc._id }).updateOne({ 
        "$set": { "Cost": newCost }
    });

    counter++;
    if (counter % 1000 == 0) {
        bulk.execute(); // Execute per 1000 operations 
        // re-initialize every 1000 update statements
        bulk = db.MyCollection.initializeUnorderedBulkOp(); 
    }
})
// Clean up remaining operations in queue
if (counter % 1000 != 0) { bulk.execute(); }

---

The next example applies to the new MongoDB version 3.2 which has since deprecated the Bulk API and provided a newer set of apis using bulkWrite().

It uses the same cursors as above but creates the arrays with the bulk operations using the same forEach() cursor method to push each bulk write document to the array. Because write commands can accept no more than 1000 operations, you will need to group your operations to have at most 1000 operations and re-intialise the array when loop hit the 1000 iteration:

var cursor = db.MyCollection.find({ "Cost": { "$exists": true, "$type": 2 } }),
    bulkUpdateOps = [];

cursor.forEach(function(doc){ 
    var newCost = Number(doc.Cost.replace(/[^0-9\.]+/g,""));
    bulkUpdateOps.push({ 
        "updateOne": {
            "filter": { "_id": doc._id },
            "update": { "$set": { "Cost": newCost } }
         }
    });

    if (bulkUpdateOps.length == 1000) {
        db.MyCollection.bulkWrite(bulkUpdateOps);
        bulkUpdateOps = [];
    }
});         

if (bulkUpdateOps.length > 0) { db.MyCollection.bulkWrite(bulkUpdateOps); }