How to loop through mongodb subdocument array find the matching elements and update the element fields with new values at once

Question

For example, if a purchase order has line items like this:

and user from the client side send us line items to be updated in an array called lineItemsToUpdate and has a format like this:

[
{       unitCost: 342,
        totalQuantity: 13,
        acceptedQuantity: 6,
        rejectedQuantity: 18,
        title: 'Unbranded Concrete Pizza',
        description: 'Soft',
        variant: 5f2d5eb0195026e6dd549ef0 },
      { unitCost: 189,
        totalQuantity: 95,
        acceptedQuantity: 49,
        rejectedQuantity: 16,
        title: 'Handcrafted Rubber Cheese',
        description: 'Assurance',
        variant: 5f2d5eaf195026e6dd549b7d },
      { unitCost: 267,
        totalQuantity: 18,
        acceptedQuantity: 93,
        rejectedQuantity: 11,
        title: 'Incredible Soft Car',
        description: 'solution-oriented',
        variant: 5f2d5eb0195026e6dd549d3d },
]

here in the lineItemsToUpdate array, it's possible that the value of each element has been changed by the client and I want to update all the matching element by variant field in my sub-document if I describe it in the format of a user story:

update all lineItems "fields" according to lineItemsToUpdate array where the lineItemsToUpdate.[elem].variant === linitem.[elem].variant using $set, if possible $each and arrayFilters operators

I read some example through the mongodb documentation like this one:

db.students2.update(
   { },
   { $set: { "grades.$[elem].mean" : 100 } },
   {
     multi: true,
     arrayFilters: [ { "elem.grade": { $gte: 85 } } ]
   }
)

but the problem in here { $set: { "grades.$[elem].mean" : 100 } } is that the 100 value is constant and I want this part dynamic as I described in above.

RLD · Accepted Answer · 2020-08-17 07:37:01Z

Following is JS function you have to take help of.

function(c1,arr2){
    var arr1 = c1.next().lineItems;
    for(i=0;i<arr1.length;i++){
        for(j=0;j<arr2.length;j++){
            if(arr1[i].variant===arr2[j].variant){
                db.purchaseOrder.update(
                { },
                { $set: { "lineItems.$[elem].unitCost" : arr2[j].unitCost,"lineItems.$[elem].totalQuantity" : arr2[j].totalQuantity } .....},
                { multi: true,
                  arrayFilters: [ { "elem.variant": { $eq: arr2[j].variant } } ]
                }
                )
            }
        }
    }
}

Explanation

We have to pass 2 parameters: i) Mongo cursor and ii) An array. Cursor is the collection to be updated. For your case, it is Purchase Order. Array is lineItemsToUpdate array here.
Next we take 2 for loops to match variant field between cursor and array.
If there is a match, we update the Purchase Order using update command and $set operator.

Execution

Go to your Mongo Shell. Ensure the current database has Purchase Order collection.

Define modifyItems function as below.

var modifyItems=function(c1,arr2){  
    var arr1 = c1.next().lineItems;  
    for(i=0;i<arr1.length;i++){  
        for(j=0;j<arr2.length;j++){  
            if(arr1[i].variant===arr2[j].variant){  
                db.purchaseOrder.update(  
                { },  
                { $set: { "lineItems.$[elem].unitCost" :     arr2[j].unitCost,"lineItems.$[elem].totalQuantity" : arr2[j].totalQuantity }...    },  
                { multi: true,  
                  arrayFilters: [ { "elem.variant": { $eq:   arr2[j].variant } } ]  
                }  
                )  
            }  
        }  
    }  
}

Define a variable arr which contains lineItemsToUpdate.

var arr=[  
      { unitCost: 342,  
        totalQuantity: 13,  
        acceptedQuantity: 6,  
        rejectedQuantity: 18,  
        title: 'Unbranded Concrete Pizza',  
        description: 'Soft',  
        variant: 5f2d5eb0195026e6dd549ef0 },  
      { unitCost: 189,  
        totalQuantity: 95,  
        acceptedQuantity: 49,  
        rejectedQuantity: 16,  
        title: 'Handcrafted Rubber Cheese',  
        description: 'Assurance',  
        variant: 5f2d5eaf195026e6dd549b7d },  
      { unitCost: 267,  
        totalQuantity: 18,  
        acceptedQuantity: 93,  
        rejectedQuantity: 11,  
        title: 'Incredible Soft Car',  
        description: 'solution-oriented',  
        variant: 5f2d5eb0195026e6dd549d3d }  
]

Now execute the JS function.

modifyItems(db.purchaseOrder.find({},{"lineItems":1,"_id":0}),arr);

If there is no error, your Purchase Order line items will be now updated with the array values in one single shot.

I can do this by bulk write very easily. I want a solution in an efficient and modern way your solution if there is 100 line items this will make 100 mongo connection why should I not use bulk write instead of this also I mentioned in question title at "once" word.
100 mongo connections? It is single connection and 100 db requests. Considering you want to update with dynamic values(as you mentioned in your question, update query), it can't be done easily by bulk write, I think. Also my solution is as specified by your user story in question.

Collectives™ on Stack Overflow

How to loop through mongodb subdocument array find the matching elements and update the element fields with new values at once

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related