1

I have written millions of documents with $CURRENT_CASH_BALANCE as strings instead of decimals. How can I convert them all to decimals?

2
  • 'code' db.TESTHOLDINGS.find({CURRENT_CASH_BALANCE: {$type: "string"}}).forEach(function(obj) { obj.CURRENT_CASH_BALANCE = new NumberDecimal128(obj.CURRENT_CASH_BALANCE); db.db-name.save(obj); }); this fails on NumberDecimal128. I saw an example withNumberInt that supposedly worked. NumberDecimal also fails.
    – Ryan Scott
    Commented Apr 20, 2020 at 22:54
  • What is the MongoDB version you are using?
    – prasad_
    Commented Apr 23, 2020 at 4:01

2 Answers 2

0

Consider a collection with documents:

{ _id: 1, bal: "123.45" }
{ _id: 2, bal: "67890"}
{ _id: 3, bal: 999 }

The following command can be run from the mongo shell to update the two documents with the field bal canverted from string data type to NumberDecimal:

db.collection.find( { bal: { $type: "string" } }
).forEach( doc => { doc.bal = NumberDecimal(doc.bal); db.collection.updateOne( { _id: doc._id }, { $set: { bal: doc.bal } } ); } )

After the update, the documents:

{ "_id" : 1, "bal" : NumberDecimal("123.45") }
{ "_id" : 2, "bal" : NumberDecimal("67890") }
{ "_id" : 3, "bal" : 999 }
0

With MongoDB v4.2+, you can use $toDecimal in an update with aggregation pipeline.

db.collection.updateMany({},
[
  {
    "$set": {
      "currentCashBalance": {
        "$toDecimal": "$currentCashBalance"
      }
    }
  }
])

Mongo Playground

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.