I want to remove special character in MongoDB document

Question

I have an issue with special character in collection. I have huge number of documents in a collection. I want to loop through all the document for particular field and if there is any special character (period) present in that field value then i want to remove that special character. Please note i did not include full document structure as its huge document. Just mentioned only the field name for the reference.

Currently i have totalValue like this. I want to remove only period (.) in the value part

"totals" : {"totalValue" : "2196.65"}

I want to make it like this after the update query

"totals" : {"totalValue" : "219665"}

Please share me the details of how to fix this.

Possible duplicate of [MongoDB - strip non numeric characters in field](https://stackoverflow.com/questions/13005269/mongodb-strip-non-numeric-characters-in-field) — sudhanva, Nov 29 '19 at 08:30

score 2 · Answer 1 · answered Nov 29 '19 at 08:31

2

Try this with replace:

db.totals.find({totalValue: {$regex: "."}}).forEach(function(doc, i) {
    doc.totalValue = doc.totalValue.replace(/\./, "");
    db.totals.save(doc);
})

answered Nov 29 '19 at 08:31

Daniyal Lukmanov

1,149
10
21

prasad_ · Accepted Answer · 2019-11-29T13:16:19.917

For the input document { totals: { totalValue: "2196.65" } }, the following aggregation query will update the document to { totals: { totalValue: "219665" } }.

db.test.aggregate( [
  { $addFields: { index: { $indexOfCP: [ "$totals.totalValue", "." ] } } },
  { $match: { index: { $ne: -1 } } },
  { $project: { "totals.totalValue": { $concat: [ { $substrCP: [ "$totals.totalValue", 0, "$index" ] }, 
                                                  { $substrCP: [ "$totals.totalValue", { $add: [ "$index", 1 ] }, 99 ] } 
                                                ] 
  } } },
] ).forEach( doc => db.test.updateOne( { _id: doc._id }, { $set: { "totals.totalValue": doc.totals.totalValue } } ) );

This also updates all the documents in the collection.

Note that in case not all documents are with the "." embedded within the field, the filter is added to the query to not process those documents.

This is another way of achieving the same functionality using the updateMany method - works with MongoDB 4.2 version or later.

var queryFilter = { $cond: {
                       if: { $eq: [ { $indexOfCP: [ "$totals.totalValue", "." ] }, -1 ] },
                       then: false,
                       else: true
                     }
};

db.test.updateMany(
  { $expr: { $eq: [ queryFilter,  true ] } },
  [
    { $set: { 
          "totals.totalValue": { 
               $concat: [ 
                   { $substrCP: [ "$totals.totalValue", 0, { $indexOfCP: [ "$totals.totalValue", "." ] } ] }, 
                   { $substrCP: [ "$totals.totalValue", { $add: [ { $indexOfCP: [ "$totals.totalValue", "." ] }, 1 ] }, 99 ] } 
               ]            
          }
    } }
  ]
)

I want to remove special character in MongoDB document

2 Answers2