How to update an embedded document into a nested array?

Question

I have this kind of structure into a Mongo collection :

{
 "_id": "12345678",
 "Invoices": [
 {
 "_id": "123456789",
 "Currency": "EUR",
 "DueTotalAmountInvoice": 768.3699999999999,
 "InvoiceDate": "2016-01-01 00:00:00.000",
 "Items": [
 {
 "Item": 10,
 "ProductCode": "ABC567",
 "Quantity": 1
 },
 {
 "Item": 20,
 "ProductCode": "CDE987",
 "Quantity": 1
 }
 ]
 },
 {
 "_id": "87654321",
 "Currency": "EUR",
 "DueTotalAmountInvoice": 768.3699999999999,
 "InvoiceDate": "2016-01-01 00:00:00.000",
 "Items": [
 {
 "Item": 30,
 "ProductCode": "PLO987",
 "Quantity": 1,
 "Units": "KM3"
 },
 {
 "Item": 40,
 "ProductCode": "PLS567",
 "Quantity": 1,
 "DueTotalAmountInvoice": 768.3699999999999
 }
 ]
 }
 ]
}

So I have a first object storing several Invoices and each Invoice is storing several Items. An item is an embedded document. So in relational modelisation : A customer has 1 or several Invoice An Invoice has 1 or several Item

I am facing an issue since I am trying to update a specific Item into a specific a specific Invoice. For example I want to change the quantity of the item 10 in Invoice 123456789.

How is it possible to do that in Mongodb ?

I tried :

• Push statement but it doesn't seem to work for nested arrays

• arrayFilters but it doesn't seem to work for embedded document in nested arrays (only simple value arrays).

Can you give me some advice about it ?

Thank you !

• s7vr: what is your mongo server version ? run db.version() to know the version. can you show the query with array filters ?
• Rahul Raj: @Junayy, arrayFilters wont work properly unless you're executing the query from latest mongo shell. Also the statement I tried arrayFilters but it doesn't seem to work for embedded document in nested arrays isnt true.
• Junayy: @Veeram I am on mongo 3.6
• Rahul Raj: Are u executing in a mongo shell or third party tool?
• Junayy: @Rahul Raj mongo shell

Answer 1

As per your problem description here:

For example I want to change the quantity of the item 10 in Invoice 123456789. I just changed the Quantity to 3. You can perform any operations here as you want. You just need to take note of how I used arrayFilters here.

Try this query:

db.collection.update(
 {"_id" : "12345678"},
 {$set:{"Invoices.$[element1].Items.$[element2].Quantity":3}},
 {multi:true, arrayFilters:[ {"element1._id": "123456789"},{ 
 "element2.Item": { $eq: 10 }} ]}
)

The above query successfully executed from mongo shell (Mongo 3.6.3). And I see this result:

/* 1 */
{
"_id" : "12345678",
"Invoices" : [ 
 {
 "_id" : "123456789",
 "Currency" : "EUR",
 "DueTotalAmountInvoice" : 768.37,
 "InvoiceDate" : "2016-01-01 00:00:00.000",
 "Items" : [ 
 {
 "Item" : 10,
 "ProductCode" : "ABC567",
 "Quantity" : 3.0
 }, 
 {
 "Item" : 20,
 "ProductCode" : "CDE987",
 "Quantity" : 1
 }
 ]
 }, 
 {
 "_id" : "87654321",
 "Currency" : "EUR",
 "DueTotalAmountInvoice" : 768.37,
 "InvoiceDate" : "2016-01-01 00:00:00.000",
 "Items" : [ 
 {
 "Item" : 30,
 "ProductCode" : "PLO987",
 "Quantity" : 1,
 "Units" : "KM3"
 }, 
 {
 "Item" : 40,
 "ProductCode" : "PLS567",
 "Quantity" : 1,
 "DueTotalAmountInvoice" : 768.37
 }
 ]
 }
 ]
}

enter image description here

Is that what you wanted?

Answer 2

Mongo Db has a way to get the specific array element by using its index. For example, you have an array and you need to get [your] index, then in mongo we use dot . but not braces [ ] !! And one thing is important either! - If you are getting the embedded value (in object or array) you must use " " for your way so if you are changing your value inside this must be like that:

 yourModel.findOneAndUpdate(
 { _id: "12345678" },
 {
 $set: {
 "Invoices.0.Items.0.Quantity": 10,
 },
 }
 );

0 - is your element indexes in the array! $set is the operator to set new value 10 - new value

Else you can go further, you can construct your way to the value with the variable indexes. Use string template

 yourModel.findOneAndUpdate(
 { _id: "12345678" },
 {
 $set: {
 [`Invoices.${invoiceIndex}.Items.${itemIndex}.Quantity`]:newValue ,
 },
 }
 );

it is the same but you can paste variable indexes