How to update objects in array in Mongo

Question

I'm quite good at SQL, but I'm struggling with even the simplest Mongo queries.

My documents in the post collection looks like this (simplified)

{
 '_id': 5
 'body': 'Correct horse battery staple',
 'comments': 
 [{
 user_id: '123'
 }, 
 {
 user_id: '456'
 }, 
 {
 user_id: '123'
 }]
}

I need to update the user_id's that are '123' to 'abc' for all posts.

In a SQL database, I would have a post table and a comment table and do this:

UPDATE comment SET user_id = 'abc' WHERE user_id = '123'

• JohnnyHK: possible duplicate of how to update multiple array elements in mongodb

Answer 1

I think you're looking for FindAndModify:

db.runCommand({
 findAndModify: "post",
 query: { user_id: 123 },
 update: { $set: { user_id: 'abc' } }
})

Edit

I believe you can do a similar thing on a collection with update if multi is set to true:

db.post.update(
 { user_id: 123 },
 { $set: { user_id: 'abc' } },
 { multi: true }
)

• Paul Draper: I need to modify all documents, not just one. I updated my question to be more explicit.

Answer 2

By default, mongodb update command will update only one document.

db.collection.update({document to be found},{changes to be done})

To update multiple document, you should include multi keyword.

db.collection.update({document to be found},{changes to be done},{multi:true})

Assuming your document structure as below:

{
 "_id": 5,
 "body": "Correct horse battery staple",
 "comments": [{"user_id": "123"},{"user_id": "456"},{"user_id": "123"}]
}
{
 "_id": 6,
 "body": "Correct horse battery staple",
 "comments": [{"user_id': "23"},{"user_id": "123"},{"user_id": "13"}]
}

In this case, i may need to update multiple elements inside an array as well as multiple documents. There is no default mongodb query to do it. Instead i will loop through documents and do it as follows.

// loop until all documents has been updated
while(db.post.find({'comments.user_id':'123'}).count()!=0) 
{
 db.post.update(
 { 'comments.user_id':'123' },
 { $set:{'comments.$.user_id':'abc'} },
 { multi:true }
 )
}

After the 1st loop run, post collection will look like:

{
 "_id": 5,
 "body": "Correct horse battery staple",
 "comments": [{"user_id": "abc"},{"user_id": "456"},{"user_id": "123"}]
}
{
 "_id": 6,
 "body": "Correct horse battery staple",
 "comments": [{"user_id": "23"},{"user_id": "abc"},{"user_id": "13"}]
}

After the 2nd loop run, post collection will look like:

{
 "_id": 5,
 "body": "Correct horse battery staple",
 "comments": [{"user_id": "abc"},{"user_id": "456"},{"user_id": "abc"}]
}
{
 "_id": 6,
 "body": "Correct horse battery staple",
 "comments": [{"user_id": "23"},{"user_id": "abc"},{"user_id": "13"}]
}

In the third loop run, the loop gets terminated.

Answer 3

You can use arrayFilters from v3.6,

db.post.updateMany(
 { 'comments.user_id': '123' },
 { 
 $set: {
 'comments.$[elem].user_id': 'abc' 
 }
 },
 { 
 arrayFilters: [
 { 'elem.user_id': '123' }
 ]
 }
);