Nested arrays how to do query and update?

[Mamatha_M]

Hi Team,

I have a collection with 5000k documents with multiple nested arrays.

I want to update the prodId key from old to new value.

Some of the documents have only 1 prodId value and other documents have one or more multiple prodId values and few of the documents have no prodId value.

1. If I query like mentioned below–> I get all the prodId values available in that particular document and also the same value available in the other documents including other prodId.

db.test01.aggregate([{$unwind:"$ordDoc.custOrderItems"},
   {$match:{"ordDoc.custOrderItems.custOrdSubItems.prodId":"VU0074"}},
   {$project:{"ordDoc.custOrderItems.custOrdSubItems.prodId":1}}]).pretty()

output:

{
	"_id" : "ORN-178450914676",
{
   "ordDoc":{
      "custOrdItems":{
         "custOrdSubItems":[
            {
               "prodId":"VU0091"
            },
            {
               "prodId":"VU0074"
            },
            {
               "prodId":"VU0081"
            },
            {
               "prodId":"VU0033"
            },
           {
               "prodId": " "
            },
            {
               "prodId":"VU0038"
            }
         ]
      }
   }
}

2. If i query like below i get the required prodId value for that value queried from all the documents. --> multiple $unwind command is used:

db.test01.aggregate([{$unwind:"$ordDoc.custOrderItems"},{$unwind:"$ordDoc.custOrderItems.custOrderSubItems"},{$match:{"ordDoc.custOrderItems.custOrderSubItems.prodId":"VU0074"}},{$project:{"ordDoc.custOrderItems.custOrderSubItems.prodtId":1}}]).pretty()   

output:

{
	"_id" : "ORN-12345678900096",
	"ordDocument" : {
		"custOrderItems" : {
			"custOrderSubItems" : {
				"prodId" : "VU0074""
			}
		}
	}
},

I feel the 2nd method of querying is correct for this type of nested arrays. Correct me if I am wrong and based on this how to update one single prodId in all the documents where that prodId is available?

for example how to update prodId value from VU0074 to AC0067 available in all the 5000k documents?

Regards
Mam

[Gaetan_MORLET (Gaétan Morlet)]

Welcome to the community @Mamatha_M !

There are 2-3 errors on your queries.

But infact you don’t need the Aggregation Framework for a problem like this. You rather need the updateMany() command. We are typically requesting a data schema with an attribute pattern.

I simplified your work, I found the solution.

First, you didn’t specify if in your “custOrdSubItems” array there could be several sub-documents with the same value for the “prodId” field. So there are actually 2 solutions.

1. If you are sure, really sure, that there are no duplicates as explained above you can use the $(update) operator. If there are duplicates in the array it will just update the first subdocument.
   Here is the query:

db.TestCollection.updateMany({“ordDoc.custOrdItems.custOrdSubItems.prodId”: “VU0074”},
{$set: {“ordDoc.custOrdItems.custOrdSubItems.$.prodId”: “newValue”}})

2. If you have duplicates, or maybe one day you will, the safest method is to use the $[identifier] operator like this.

db.TestCollection.updateMany({},
{$set: {“ordDoc.custOrdItems.custOrdSubItems.$[element].prodId”: “newValue”}},
{arrayFilters: [{“element.prodId”: “VU0074”}],multi: true})

More elegant than an aggregation query, right ?

If you have any questions don’t hesitate.

[Prasad_Saya]

@Mamatha_M

for example how to update prodId value from VU0074 to AC0067 available in all the 5000k documents?

The solution 2 by @Gaetan_MORLET works fine.

@Gaetan_MORLET

If you are sure , really sure , that there are no duplicates as explained above you can use the $(update) operator. If there are duplicates in the array it will just update the first subdocument.

This is about the solution 1. This will not work. The $(update) operator cannot be applied for nested arrays. From the documentation:

Nested Arrays
The positional $ operator cannot be used for queries which traverse more than one array, such as queries that traverse arrays nested within other arrays, because the replacement for the $ placeholder is a single value

[Mamatha_M]

@Gaetan_MORLET

db.TestCollection.updateMany({“ordDoc.custOrdItems.custOrdSubItems.prodId”: “VU0074”},
{set: {“ordDoc.custOrdItems.custOrdSubItems…prodId”: “newValue”}})

I will try the 2nd query.Also can i get the find query for both these options?

[Prasad_Saya]

Correction about the update operation:

db.TestCollection.updateMany({},
{set: {“ordDoc.custOrdItems.custOrdSubItems.[element].prodId”: “newValue”}},
{arrayFilters: [{“element.prodId”: “VU0074”}],multi: true})

I did miss something in the update solution 2 by @Gaetan_MORLET . The correct way to do it is as follows. Note the usage of the $[ ] and the $[<someId>] array update operators.

db.collection.updateMany(
   { "ordDoc.custOrdItems.custOrdSubItems.prodId": "AC0074" },
   { $set: { "ordDoc.custOrdItems.$[].custOrdSubItems.$[e].prodId" : "AC0067" } },
   {
     arrayFilters: [ { "e.prodId": "AC0074"} ]
   }
)

Note on Array Update Operators:

• $ positional operator works fine with an array - not on nested arrays.
• $[] all positional operator is to update all elements of a nested array.
• $[<someId>] filtered positional operator is used to update nested array, based upon a specific condition on the array element.

The latter two operators must be used with update operations on nested arrays. The $[<someId>] is used in conjunction with the arrayFilters update method option.

[Gaetan_MORLET (Gaétan Morlet)]

@Mamatha_M

@Gaetan_MORLET

db.TestCollection.updateMany({“ordDoc.custOrdItems.custOrdSubItems.prodId”: “VU0074”},
{set: {“ordDoc.custOrdItems.custOrdSubItems…prodId”: “newValue”}})

I will try the 2nd query.Also can i get the find query for both these options?

You can just do that to find the documents to change.

db.collection.find({“ordDoc.custOrdItems.custOrdSubItems.prodId”: “AC0074”})

Can you also send a sample document from your collection @Mamatha_M ? We just have the return of the aggregation queries for the moment. It will allow us to see more clearly.

[Prasad_Saya]

@Gaetan_MORLET If you see the following first query and output in the original post by @Mamatha_M :

db.test01.aggregate([{$unwind:"$ordDoc.custOrderItems"},
   {$match:{"ordDoc.custOrderItems.custOrdSubItems.prodId":"VU0074"}},
   {$project:{"ordDoc.custOrderItems.custOrdSubItems.prodId":1}}]).pretty()

{
   "ordDoc":{
      "custOrdItems":{
         "custOrdSubItems":[
            {
               "prodId":"VU0091"
            },
            {
               "prodId":"VU0074"
            },
            ...
         ]
      }
}

Note the output has an array field custOrdSubItems. This is after the aggregation’s $unwind stage: {$unwind:"$ordDoc.custOrderItems"}. This clearly tells that the ordDoc.custOrderItems is an array (the $unwind is applied only on arrays).

So, the structure of the document has nested arrays. The outer array "ordDoc.custOrderItems" and the inner array "ordDoc.custOrderItems.custOrdSubItems" - and that is a nested array.

[Gaetan_MORLET (Gaétan Morlet)]

Arf, I was editing my post
Here is what I wanted to write:

I thought the custOrdItems field was an object … so we didn’t have nested arrays.
But the output of the first query + the unwind, we can effectively think that it’s rather an array.

You are absolutely right. I based myself too much on the result of the query.

[Mamatha_M]

i want to know where i can share as it is a big document.

[Gaetan_MORLET (Gaétan Morlet)]

You can share your document via pastebin.com if necessary @Mamatha_M.

[Mamatha_M]

Hi Prasad,

The find query which i had provided was returning almost 944 docs from that collection.
The update query which provided also updated 944 docs.
I guess it works but still i trying the same approach on the other nested arrays.
Now i would like to understand what it the function of this [] , [e] actually does?
and in arrayFilters u have provided e.prodId --> that point i didnt get it as I am new to mongodb. Can you please help me here?

[Prasad_Saya]

Assume your input document is as following. There is an outer array custOrdItems within the ordDoc sub-document; the array’s path is "ordDoc.custOrdItems". Then, there is the array "ordDoc.custOrdItems.custOrdSubItems", and this is referred as nested array (an array within an array, the inner array).

{
   "ordDoc": {
      "custOrdItems": [
         "custOrdSubItems":[
            {
               "prodId": "VU0091"
            },
            {
               "prodId": "VU0074"
            },
            ...
         ]
      ]
    }
}

Now, you want to update the nested array "ordDoc.custOrdItems.custOrdSubItems"'s element, which is a sub-document { "prodId": "VU0074" }. The update has the condition that this sub-document’s prodId field value must be “VU0074”, and this is to be updated to a new value.

The update operations on nested arrays with condition use the updateMany method’s option arrayFilters. The arrayFilters specifies the condition by which the nested array is to be updated - in this case the condition is that the prodId's value must be equal to “VU0074”. And, what about the condition for the outer array custOrdItems? There are no conditions there, and it means all the elements of this array are updateable.

The update statement:

db.collection.updateMany(
   { "ordDoc.custOrdItems.custOrdSubItems.prodId": "AC0074" },
   { $set: { "ordDoc.custOrdItems.$[].custOrdSubItems.$[e].prodId" : "AC0067" } },
   {
     arrayFilters: [ { "e.prodId": "AC0074"} ]
   }
)

The $set update operator updates a field’s value. In this case, we have to update the inner array’s field value based upon the condition (as discussed above). The update field’s path is specified as "ordDoc.custOrdItems.$[].custOrdSubItems.$[e].prodId".

The two array update operators, the $[] and the $[e] are used here. The $[] operator is used when there is no condition on the array element, in this case the outer array. The ordDoc.custOrdItems.$[] portion of the update field path says that.

The remaining part of the path, custOrdSubItems.$[e].prodId says that the inner array element’s field to be updated. The e of the $[e] specifies the sub-document’s field for the condition, which is used with the arrayFilters. The e can be of any name (it is user defined, it could be subItem for example). The string “AC0067” is the new value to be updated with.

Please go thru the documentation (the links I had provided earlier), and browse the details and the examples within.

[Mamatha_M]

Thanks for the detailed explanation.

[Mamatha_M]

if i want to create an index on prodId – what index should go in and how should i create the index for arrays?

[Prasad_Saya]

Array field values are indexed using Multikey Indexes.

[Mamatha_M]

Hi Prasad,

Nested arrays for the below aggregate query I am not able to update the key value to a newer value.I have put the script here and json format output. Can you pls help me how to update?

db.test01.aggregate([{$unwind:"$ordDoc.custOrderItems"},
{$unwind:"$ordDoc.custOrderItems.custOrdSubItems"},
{$unwind:"$ordDoc.custOrderItems.custOrdSubItems.prodAtt"},
{$unwind:"$ordDoc.custOrderItems.custOrdSubItems.prodAtt.prices"},
{$unwind:"$ordDoc.custOrderItems.custOrdSubItems.prodAtt.prices.Key"},
{$match:{"ordDoc.custOrderItems.custOrdSubItems.prodAtt.prices.Key":"Technology=NONE"}},
{$project:{"ordDoc.custOrderItems.custOrdSubItems.prodAtt.prices.Key":1}}]).pretty()

JSON:

/* 1 */
{
	"_id" : "ORN-1628755216489",
	"ordDoc" : {
		"custOrderItems" : {
			"custOrderSubItems" : {
				"prodAtt" : {
					"prices" : {
						"Key" : "Technology=NONE"
					}
				}
			}
		}
	}
},

/* 2 */
{
	"_id" : "ORN-3091717461503",
	"ordDoc" : {
		"custOrderItems" : {
			"custOrderSubItems" : {
				"prodAtt" : {
					"prices" : {
						"Key" : "Technology=NONE"
					}
				}
			}
		}
	}
},

[Mamatha_M]

Team ,

Any updates for the above ask?

[Mamatha_M]

Any updates for the above ask?