Search code examples
mongodbnumbersquotes

Remove quotes from MongoDB field

I have a MongoDB collection with many records like this

{ 
"_id" : "500cecd97f3b198ba7aceea3",
        "startDate" : NumberLong("1343023785531"),
        "user_id" : "soggettoDGcfe",
        "app_source" : "Diary",
        "measuredValue":120,
}

The problem is that due to a bug of an application, there are hundreds of records with the field "measuredValue" as a string:

{ 
"_id" : "ab0cecd97f3176eba7aceea3",
        "startDate" : NumberLong("1343023785531"),
        "user_id" : "soggettoDGcfe",
        "app_source" : "Diary",
        "measuredValue":"76.0",
}

So when i try to run some queries to check if the value is bigger than something, the record is not returned. With a query i was able to correct some records one by one 

db.OBSERVABLEPARAMETER.update({_id:"500cecd97f3b198ba7aceea3"},{$set:{measuredValue:76}});

but they are hundreds! How can i update all of them rapidly?

Solution

  • If all the values are the same (that is "76.0"), then you simply do something like :

    db.OBSERVABLEPARAMETER.update({measuredValue:"76.0"},{$set:{measuredValue:76.0}}, {multi:true});

    If the values are all different strings, then you will have to find the incorrect strings with :

    c = db.OBSERVABLEPARAMETER;
c.find({measuredValue:{$type: 2}}).forEach(function (r){
    c.update({_id:r._id},{$set:{measuredValue:Number(r.measuredValue)}});
});

    If you have a large number of measurements but a limited number of measuredValue, it might be faster to use the aggregation framework to get all the individual values and then update them in batches.