MongoDB update Query in a loop

Question

I am working with MongoDB in one of my project. For the next feature I need to add a flag to array of objects by looping with a matching condition.

Following is an example of document that I have -

{
    "_id" : ObjectId("5aaa4eb211a1c1c1f74c1657"),
    "colors" : [
       { "status" : "done" },
       { "status" : "done" },
       { "status" : "n/a" }
   ]        
}

Output should be -

{
   "_id" : ObjectId("5aaa4eb211a1c1c1f74c1657"),
   "colors" : [
       {
           "status" : "done",
           "is_in_production" : true
       }, 
       {
           "status" : "done",
           "is_in_production" : true
       },
       {
        "status" : "n/a"
       }
   ]
}

I used the following query -

db.getCollection('products').update( 
   {
       "colors.status": {
          "$in":[
             "done"
          ]
       }
   },
   {$set: { 'colors.$[].is_in_production' :true }},
   {multi: true}
)

That resulted the following error -

cannot use the part(colors of colors.$[].is_in_production)totraversetheelement({ colors: [{ "status" : "done" }, { "status" : "done" }, { "status" : "n/a" }] )}

Can someone help me with the query?? Thanks in Advance

First of all, please check your Mongo version. I ran your query on my local (MongoDB 3.6.3) and I see results: 1) https://i.stack.imgur.com/6dfMi.png 2) https://i.stack.imgur.com/DbMta.png , So your query is wrong. — Rahul Raj, Apr 13 '18 at 06:02
Mate, the result that you have, shows all the objects with colors array with "is_in_production" : true. I want the flag to be added only for the objects with "status" : "done". And I am using MongoDB shell version v3.4.7 — Bishwarup Das, Apr 13 '18 at 06:10
You need to upgrade your version and I have updated your query in the below answer. Hope it helps! — Rahul Raj, Apr 13 '18 at 06:15

score 2 · Answer 1 · answered Apr 13 '18 at 07:16

2

Use updateMany together with arrayFilters to update multiple documents in an array

db.products.updateMany(
   { "colors.status": "done" },
   { $set: { "colors.$[element].is_in_production" : true}},
   { arrayFilters: [{"element.status" : "done"}]}
)

answered Apr 13 '18 at 07:16

Clement Amarnath

5,301
1
21
34

OP is asking if an alternate method is available other than `arrayFlter` since it has tight dependency on v3.6. OP is using v3.4 and he says it may be a risk upgrading to higher version. – Rahul Raj Apr 13 '18 at 07:23
This post has a lot of other options than arrayFilters please have a look into it - https://stackoverflow.com/questions/4669178/how-to-update-multiple-array-elements-in-mongodb – Clement Amarnath Apr 13 '18 at 09:39
Positional operators `$[]` are giving incorrect results and that's why left with `arrayFilters` – Rahul Raj Apr 13 '18 at 09:45
I copied the DB in test server, and it worked. Thanks for your help!! – Bishwarup Das Apr 13 '18 at 10:47

Rahul Raj · Answer 2 · 2018-04-13T06:52:11.497

1

Use arrayFilters to update only a subset of embedded documents.

Try this query:

db.products.update(
{
   "colors.status": {
      "$in":[
         "done"
        ]
     }
  },
  {$set: { 'colors.$[element].is_in_production' :true }},
  {multi: true, arrayFilters:[{"element.status":"done"}]}
)

Output is:

/* 1 */
{
"_id" : ObjectId("5aaa4eb211a1c1c1f74c1657"),
"colors" : [ 
    {
        "status" : "done",
        "is_in_production" : true
    }, 
    {
        "status" : "done",
        "is_in_production" : true
    }, 
    {
        "status" : "n/a"
    }
  ]
}

Note that you need MongoDB 3.6 and need to execute on latest shell (no third party tools preferably) in order for the arrayFilters to work properly.

If the above query isn't working for your Mongo version, try this query:

db.sample.findAndModify({
  query:{
     "colors.status": {
        "$in":[
           "done"
        ]
     }
 },
 update:{$set: { 'colors.$[element].is_in_production' : true }},
 multi: true, 
 arrayFilters:[{"element.status":"done"}]
})

edited Apr 13 '18 at 06:52

answered Apr 13 '18 at 06:14

Rahul Raj

3,197
5
35
55

Any other way to rewrite the same query?? I am running MongoDb on Docker. It will take a lot of time to upgrade. Or might break other container :( – Bishwarup Das Apr 13 '18 at 06:31
Updated the answer with an alternate query. Hope that helps! But I still suspect `arrayFilter` part may not work since there are some updates to `findAndModify` on v3.6 – Rahul Raj Apr 13 '18 at 06:52
Also note that your previous query on positional operators `$[]` , is not working (Forget the fact that it was wrong). Its because of version issues. The issues you were facing were already reported in the past by users and thats why they have fixed them in latest version along with lots of features. – Rahul Raj Apr 13 '18 at 07:08
I copied the DB in test server, and it worked. Thanks for your help!! – Bishwarup Das Apr 13 '18 at 10:48

MongoDB update Query in a loop

2 Answers2