Mongo convert all numeric fields that are stored as string

Question

It looks like for a while now I've been storing my decimals as strings. This is now a problem because I need to start using the aggregation framework to perform some calculations.

Is there any way to walk each document in my collection and check each value for isNaN, and if false, store it with parseFloat

Answer 1

Something like this should work from the mongo shell:

db.yourCollection.find({}).forEach(function(doc) { 
    if(isNaN(doc.xyz)) { 
        print('found string: ' + doc._id);
        db.yourCollection.update( 
           { _id: doc._id}, 
           { $set : { "xyz" : parseFloat(doc.xyz) } }
        )
    }
})

It loops through each document, uses isNaN as you suggested, then $sets the value to the parseFloat value for the current document.

Answer 2

Thanks for your response. I was unclear in my initial question in that I did not want to have to specify the fields to update to numbers, but rather walk each property and perform the check.

I have put together this little function to do the trick:

var cursor = db.results.find();
while (cursor.hasNext()) {
    var doc = cursor.next();
    for (key in doc) {
        if (key.match(/^metric_.*/i) && !isNaN(doc[key])) {
            doc[key] = parseFloat(doc[key]);
            db.results.update({ _id : doc._id }, doc );
        }
    }
}

Two things to note:

1. This only does the check and conversion to the first level of properties. It can be easily modified to go deeper, but this was all I needed at this time.
2. This function will blast your date values away!!! A javascript date is evaluated by isNaN as false, and therefore will be treated as a number. In my case, using parseFloat will actually set the value to isNaN. Again, a very easy fix for the onlooker, but I just wanted to provide that disclaimer.

Hope this helps someone else in my position.