Updating values in nested arrays in MongoDB

Question

I found this answer https://stackoverflow.com/a/12636038/11792 to be very helpful to me how to query nested arrays. What I want now is to be able to update (push or replace) a value in the nested array. For example I want to replace all the "apple" values with "peach".

db.multiArr.insert({"ID" : "fruit1","Keys" : [["apple", "carrot", "banana"]]})
db.multiArr.insert({"ID" : "fruit2","Keys" : [["apple", "orange", "banana"]]})

I found 3 more questions about updating nested arrays but their answers do not seem to be helpful in this case. So how do we modify the values in the inner array?

Answer 1

I think what you're looking for is findAndyModify. See the documentation here.

Basically, you can include a query, remove an item, and update the document in one go. For example, if you want to:

1. Find all documents with 'apple' in the Keys subdocument
2. Remove all instances of 'apple'
3. Add 'peach' at the end of the array

It might look something like this:

db.multiArr.findAndModify({
    query: { Keys : 'apple' },
    update: { $pull : { Keys : 'apple' }}, { $push : { Keys : 'peach' }},
    new: true
});

EDIT:

The above looks like it should work, but you can't use $push and $pull together in one update. It produces this error message:

findAndModifyFailed failed: {
    "errmsg" : "exception: Field name duplication not allowed with modifiers",
    "code" : 10150,
    "ok" : 0
} at src/mongo/shell/collection.js:399

You're just going to have to do it in two updates:

// $push first or you won't be able to find the documents
db.multiArr.update({ Keys : 'apple' }, { $push : { Keys : 'peaches' }}, { multi : true });
db.multiArr.update({ Keys : 'apple' }, { $pull : { Keys : 'apple' }}, { multi : true });

Answer 2

To add to the previous answer, if you know the index of 'apple', you may hard code as below:

db.multiArr.update({Keys:"apple"}, {$set: {"Keys.0":"peaches"}}, {multi:true})