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

Working with Data
, ,
1

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
2

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.

4

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.

5

That kind of requirements should be stated first.

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.