MongoDB update inside 2 nested arrays of objects

Question

I'm currently developing an application we are using mongodb I'm new to it and I faced many challenges when it comes to complicated updates

This is our collection orders :

{
   "_id" : ObjectId(),
   "company" : String,
   "employee" : String,
   "office" : String,
   "status" : String,
   "subOrders" : [ 
    {
        "products" : [ 
            {
                "productId" : ObjectId(),
                "name" : String,
                "price" : String,
                "status" : String
            }
        ],
        "tax" : Int,
        "subTotal" : Int,
        "total" : "Int,
        "status" : String,
        "orderNote" : String
    }
  ]
}

We need to commit an update as below

   update the subOrders.products.status to something ( for example : delivered )

where

company = "x company" and office = "y office" and subOrders.products.productId = "z id"

can anyone please provide us a clean code that can handle this query

please don't provide links to weblogs or websites unless they are targeting a very similar issue

thanks in advance

score 1 · Accepted Answer · edited May 23 '17 at 12:28

You could try something like this (It will be great, but... look at question update part):

db.coll.update(
    { company: "x company", office: "y office", "subOrders.products.productId": "z id"},
    { $set: { "subOrders.$.products.$.status": "delivered" } }
)

Also you could read this documentation about update operations: http://docs.mongodb.org/manual/reference/method/db.collection.update/

Update:
Ops.. Sorry, but positional operator ($) don't work with nested arrays... You can see it on JIRA and also in documentation.
Some related questions: 1, 2
You can use "subOrders.$.products.0.status" instead of "subOrders.$.products.$.status" if it is possible in your case. But it will be better if you add some changes to your schema and get rid of the need to update documents in nested arrays.
But in general update syntax looks like as shown earlier.

Update 2:
For find only one document and update status of only one product in only one subOrder you could do something like this:

var productIdForUpdate = "z id";
var doc = db.coll.findOne({ company: "x company", office: "y office", "subOrders.products.productId": productIdForUpdate });
top:
for(var i = 0; i < doc.subOrders.length; i++) {
    var subOrder = doc.subOrders[i];
    for(var j = 0; j < subOrder.products.length; j++) {
        var product = subOrder.products[j];
        if (product.productId == productIdForUpdate) {
            eval('db.coll.update({ _id: doc._id }, { $set: { "subOrders.' + i + '.products.' + j + '.status": "delivered" } } )');
            break top;   
        }
    }    
}

or:

var productIdForUpdate = "z id";
var doc = db.coll.findOne({ company: "x company", office: "y office", "subOrders.products.productId": productIdForUpdate });
top:
for(var i = 0; i < doc.subOrders.length; i++) {
    var subOrder = doc.subOrders[i];
    for(var j = 0; j < subOrder.products.length; j++) {
        var product = subOrder.products[j];
        if (product.productId == productIdForUpdate) {
            product.status = "delivered";
            db.coll.save(doc);
            break top;   
        }
    }    
}

Shown code updating only one product with particular index in only one subOrder. If you want to update more than one document or more than one subOrder... or more than one product, you should add some changes to this code.

Thanks for editing the answer I noticed as well and I realized that $ operator for nested arrays not working, the second option is possible if we have the index of the exact products we need to update, so let's change the question to this: If we have all the above criteria I mean : { company: "x company", office: "y office", "subOrders.products.productId": "z id" } Is it possible to get the index of the subOrders.products array that matches the criteria and then with the help of index update that — Siavosh, Feb 22 '14 at 08:27
Thanks for your answer although it needed a bit of modification to get it work — Siavosh, Feb 24 '14 at 18:37

MongoDB update inside 2 nested arrays of objects

1 Answers1