Bulk Edit Document Data from String to Float

I collect data on item prices in my MongoDB collection and I made a huge mistake and stored them all as a string like { “price”: “$10.99”}. I have about 10,000 documents like this and I want to convert them all into an float like { “price”: 10.99} but I am not sure how to do this. I use NodeJS and/or Python.

Hello @dzz, welcome to the MongoDB Community forum!

You can write an Aggregation Query to convert the data from the string type to double. First, use the $substrCP operator to get the number part of the string “$10.99” as “10.99”. Then apply the $toDouble operator to convert the string to the floating point value (there is no “toFloat” operator).

To perform this as an update operation (i.e., change the values in the database, use the Updates With Aggregation Pipeline feature). For doing a bulk update use the db.collection.updateMany() method.

You can do this operation using the MongoDB NodeJS Driver APIs (JavaScript code) or the PyMongo Driver (Python code) APIs.

2 Likes

If I may add to @Prasad_Saya solution, here is what I like to do in bulk situation like yours.

Rather than updating the source collection, I use https://docs.mongodb.com/manual/reference/operator/aggregation/out/ to store the results into a temporary collection. Once I am happy that I get the results I really wanted I just swap the source collection with the temporary results. This way if I screw up it is easier to do any correction. Otherwise, if the source collection is corrupted, you would need to upload it from backup.

1 Like