How to update string field in mongodb and manipulate string values?

Question

I have a MongoDB collection with some documents that have a field called Personal.FirstName and another field call Personal.Surname. Some documents are messed up and have the persons first name and last name in both fields. For example there are some documents that have Personal.FirstName = 'John Doe' and Personal.Surname = 'John Doe'.

I want to write a mongo update statement that will do the following:

Find all of the documents that have a Personal section
Find all of the documents where Personal.FirstName == Personal.Surname
Update Personal.FirstName to be just the first part of Personal.FirstName before the space
Update Personal.Surname to be just the second part of Personal.Surname after the space

Is this possible in a mongo update statement? I am new to mongo and know very little about how to query it.

EDIT: here is an example document

{
    "_id" : LUUID("fcd140b1-ec0f-0c49-aa79-fed00899290e"),
    "Personal" : {
        "FirstName" : "John Doe",
        "Surname" : "John Doe"
    }
}

Can you edit your question to show some sample documents from the collection which show the document structure? — chridam
– chridam, Commented Jan 16, 2017 at 17:49

felix · Accepted Answer · 2017-01-16 18:25:49Z

4

you can't do this in a single query, but you can achieve this by iterating over result like this :

db.name.find({$and: [{Personal: {$exists: true}}, {$where: "this.Personal.FirstName == this.Personal.Surname"}]}).forEach(function(e,i){
    var parts = e.Personal.FirstName.split(" ");
    e.Personal.FirstName = parts[0];
    e.Personal.Surname = parts[1];
    db.name.save(e); 
})

result:

{ "_id" : "fcd140b1-ec0f-0c49-aa79-fed00899290e", "Personal" : { "FirstName" : "John", "Surname" : "Doe" } }

answered Jan 16, 2017 at 18:25

felix

9,3158 gold badges44 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Corey Burnett Over a year ago

So can I just execute this like I would any other query in robomongo? I tried running it in robomong. It said that the script executed fine and it took like 1 second to run. And I notice that my collection still has tons of documents with FirstName == Surname

felix Over a year ago

@CoreyBurnett yes, you can. Just make sure to use the correct collection name. Here I used "name", but I guess that your collection has a different name

Corey Burnett Over a year ago

Thanks. That seems to work. However it doesn't seem to modify all of the matching records when you run it. It seems like it only does a certain number at one time. Is there any way to get robomongo to update all of the matching records?

Corey Burnett Over a year ago

Never mind. I was doing something wrong. It is working now.

chridam · Accepted Answer · 2017-01-16 19:41:00Z

The idea is get a subset of the documents from your collection by filtering the documents that match the specified criteria. Once you get the subset you iterate the list and update each document within a loop.

Now, to get the subset, you need to run an aggregation pipeline which is faster than doing a filter using find() and $where operator. Take the following example aggregate() operation which uses $redact as the filtering mechanism and then a $project pipeline to create an additional field that you can use in your update. The cursor from the aggregate() method containing the results can then be iterated with its forEach() method and subsequently update the collection on the documents from the subset:

db.collection.aggregate([
    {
        "$redact": {
            "$cond": [
                {
                    "$and": [
                        { "$eq": [ "$Personal.FirstName", "$Personal.Surname" ] },
                        {
                            "$gt": [
                                {
                                    "$size": {
                                        "$split": ["$Personal.FirstName", " "]
                                    }
                                },
                                0
                            ]
                        }
                    ]
                },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    },
    {
        "$project": {
            "FirstName": {
                "$arrayElemAt": [
                    { "$split": ["$Personal.FirstName", " "] },
                    0
                ]
            },
            "Surname": {
                "$arrayElemAt": [
                    { "$split": ["$Personal.FirstName", " "] },
                    1
                ]
            }
        }
    }
]).forEach(function(doc) {
    db.collection.updateOne(
        { "_id": doc._id },
        {
            "$set": {
                "Personal.FirstName": doc.FirstName,
                "Personal.Surname": doc.Surname,
            }
        }
    )
})

Using the aggregation framework with the $redact pipeline operator allows you to process the logical condition with the $cond operator and uses the special operations $$KEEP to "keep" the document where the logical condition is true or $$PRUNE to "remove" the document where the condition was false.

This should improve in performance significantly because the $redact operator uses MongoDB's native operators whilst a query operation with the $where operator calls the JavaScript engine to evaluate Javascript code on every document and checks the condition for each, thus can be very slow as MongoDB evaluates non-$where query operations before $where expressions and non-$where query statements may use an index.

Collectives™ on Stack Overflow

How to update string field in mongodb and manipulate string values?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related