I want to remove special character in the mongoDB document

Question

I have collection with many documents. I want to remove the special character in all the document.

My document is like as shown below. There is period in the value for all name apple , orange and banana.

{
  "_id" : ObjectId("54fdfdfdf54fdfg5474"),
  "someField" : "something",
  "SomeString" : "something",
  "SomeMoreSting" : "SomeMore",
  "fields" : [{
      "name" : "apple",
      "value" : ".200"
    }, {
      "name" : "orange",
      "value" : "431736.78"
    }, {
      "name" : "banana",
      "value" : "20.25"
    }]
}

After executing the update query, I want the document to look like below. Here period is removed in the value field for apple and orange. But period is not removed for banana. I want to remove this for specific field value but not for all the field value in this collection for all the documents. Please let me know how i can achieve this.

{
  "_id" : ObjectId("54fdfdfdf54fdfg5474"),
  "someField" : "something",
  "SomeString" : "something",
  "SomeMoreSting" : "SomeMore",
  "fields" : [{
      "name" : "apple",
      "value" : "200"
    }, {
      "name" : "orange",
      "value" : "43173678"
    }, {
      "name" : "banana",
      "value" : "20.25"
    }]
}

Frederick Zhang · Answer 1 · 2019-12-03T10:39:23.420

You can use aggregation pipeline to achieve this. For instance:

db.collection.aggregate([{
    $match: {

    }
}, {
    $project: {
        fields: {
            $map: {
                input: "$fields",
                as: "field",
                "in": {
                    $cond: {
                        "if": {
                            $in: ["$$field.name", ["apple", "orange"]]
                        },
                        then: {
                            name: "$$field.name",
                            value: {
                                $concat: [{
                                    $arrayElemAt: [{
                                        $split: ["$$field.value", "."]
                                    }, 0]
                                }, {
                                    $arrayElemAt: [{
                                        $split: ["$$field.value", "."]
                                    }, 1]
                                }]
                            }
                        },
                        "else": "$$field"
                    }
                }
            }
        }
    }
}, {
    $out: "collection2"
}])

This assumes that there is always one and only one . in fields.value. If your real data is more complex, I'd suggest using e.g. Mongo Shell, pymongo to do this, e.g. see How to replace substring in mongodb document

Thanks for the reply. I tried above query but getting below error E QUERY [js] TypeError: db.myCollection.aggregate(...).match is not a function : @(shell):1:1 — mike, Dec 03 '19 at 09:08
@OneTwo Sry I was using a query builder. I've updated my answer to use Mongo Shell syntax. — Frederick Zhang, Dec 03 '19 at 09:23
Frederick Zhang. This works but i have few fileds after "_id" : ObjectId("54fdfdfdf54fdfg5474"), those fields are missing in the new document. Is there a way to ensure those also comes to newly created document. I have updated my query — mike, Dec 03 '19 at 11:49
@OneTwo Simply replace $project with $addFields. See https://docs.mongodb.com/manual/reference/operator/aggregation/addFields/ — Frederick Zhang, Dec 03 '19 at 12:29

Sathish · Accepted Answer · 2019-12-03T10:08:47.043

Here is an approach based on JavaScript as mentioned in this below answer

StackOverFlow Question - How to Update Multiple Array Elements in mongodb

One of the answer - https://stackoverflow.com/a/20601288/3704501

Remove 1st dot

db.fruits.find({}).forEach(function (doc) {
    doc.fields.forEach(function (field) {
      if ((field.name === "apple" || field.name === "orange") && field.value.match("^[.]")) {
        field.value=field.value.substr(1,field.value.length);
      }
    });
    db.fruits.save(doc);
  });

Remove dots anywhere in the string

db.fruits.find({}).forEach(function (doc) {
    doc.fields.forEach(function (field) {
      if ((field.name === "apple" || field.name === "orange") && field.value.match("[.]")) {
        field.value=field.value.replace(".","");
      }
    });
    db.fruits.save(doc);
  });

I tried this in mongodb version 3.6.13

I want to remove special character in the mongoDB document

2 Answers2