UpdateOne Using Aggregation Pipeline On Nested Array Fields Leads To Unexpected Outcome

Arnav_Nag · 2024-08-12T15:38:55.594Z

Hi Team,

I am trying to update a nested array field using UpdateOne with aggregation pipeline but it leads to unexpected outcome. Please note that I could’ve using UpdateOne with Array Filters but doesn’t generate correct audits in case of multiple matches.

In the below document, I want to set isPrimary as true for all facilitators in 0th index of acquirers list. But the given query unexpectedly sets “0” as a key for each acquirer.

Existing Document:

{ "_id": 1, "acquirers": [ { "type": "COMPANY", "isPrimary": true, "id": "a", "facilitators": [ { "notablePeople": [ "a" ], "type": "COMPANY", "isPrimary": true, "id": "a" }, { "notablePeople": [ "a" ], "type": "COMPANY", "isPrimary": true, "id": "a" }, { "notablePeople": [ "a" ], "type": "COMPANY", "isPrimary": true, "id": "a" } ] }, { "type": "COMPANY", "isPrimary": true, "id": "a", "facilitators": [ { "notablePeople": [ "a" ], "type": "COMPANY", "isPrimary": true, "id": "a" }, { "notablePeople": [ "a" ], "type": "COMPANY", "isPrimary": true, "id": "a" }, { "notablePeople": [ "a" ], "type": "COMPANY", "isPrimary": true, "id": "a" } ] } ] }
`

Query:

`
db.acquisitionV3.updateOne( { "_id": 1, "acquirers.0.facilitators.id": "a", "acquirers.0.facilitators.type": "COMPANY" }, [ { $set: { "acquirers.0.facilitators": { $map: { input: "$acquirers.0.facilitators", as: "facilitator", in: { $cond: [ { $and: [ { $eq: ["$facilitator.id", "a"] }, { $eq: ["$facilitator.type", "COMPANY"] } ] }, { $mergeObjects: [ "$facilitator", { isPrimary: false } ] }, "$facilitator" ] } } } } } ] )
`

Updated Document:

`
{ "_id": 1, "acquirers": [ { "0": { "facilitators": [] }, "type": "COMPANY", "isPrimary": true, "id": "a", "facilitators": [ { "notablePeople": [ "a" ], "type": "COMPANY", "isPrimary": true, "id": "a" }, { "notablePeople": [ "a" ], "type": "COMPANY", "isPrimary": false, "id": "a" }, { "notablePeople": [ "a" ], "type": "COMPANY", "isPrimary": false, "id": "a" } ] }, { "0": { "facilitators": [] }, "type": "COMPANY", "isPrimary": true, "id": "a", "facilitators": [ { "notablePeople": [ "a" ], "type": "COMPANY", "isPrimary": true, "id": "a" }, { "notablePeople": [ "a" ], "type": "COMPANY", "isPrimary": false, "id": "a" }, { "notablePeople": [ "a" ], "type": "COMPANY", "isPrimary": false, "id": "a" } ] } ] }
`

Thanks

steevej · 2024-08-14T14:10:39.156Z

I do not think that dot notations for arrays is compatible for update with aggregation, like your


[ { $set : ... } ]

When I need to work on a specific array element, like facilators.0, I use $slice to isolate the element I want to work with, then $concatArrays to reassemble the array.

It usually gives tidier code compared to $map.

Arnav_Nag · 2024-08-16T06:35:34.359Z

Hi Steeve,

Thanks for your reply.

I am trying to write a generic library code for a CRUD operation that could work for any type of data.

For instances, I should be able to make changes at a List Of List at the nth level (here acquirers.0.facilitators) without having to write a $slice directive for each level. The query would become complicated with each level of array nesting.

Instead if mongoDB could understand the dot notations like it does while updating using $set and array filters, it would make the query much simpler and easier to generify.

Example query with $set and array filter:

db.getCollection("test").updateOne( {_id:1}, {"$set": {"list.$[element].position":3} }, {"arrayFilters":[ {"element.key1":"a"} ] } )

I could have used this query instead but there’s a problem with the change stream being generated when multiple matches & updates happen. I have posted another topic for that.

steevej · 2024-08-20T12:48:25.559Z

That kind of requirements should be stated first.

Arnav_Nag:

I am trying to write a generic library code for a CRUD operation that could work for any type of data.

Because when working on specific issues like your OP we will always provide ideas that solve the specific issues.

Look at $getField and $setField as they may work for your dot notation issue. But generic stuff is outside the realm of my expertise so hopefully someone can jump in your bandwagon.