How to update multiple documents efficiently based on the value of nested property?

user_8870206 0
Viewed 30

Okay so I need to update documents based on the values of its nested property values. Suppose I have this document:

{
     "_id": "61e3050a465c380aec9f56a1",
     "name": "John Doe",
     "status": "pending",
     "payment": [
             {
                  "status": "completed"
             }, 
             {
                  "status": "completed"
             }
     ]
}

I need to find documents where all payment.status is completed and when documents are found, then I need to update the outer status to completed.

I can do this in multiple queries and filter out the ones which are completed but it makes a lot of queries based on the amount of documents which I think is not efficient performance wise. So what I was thinking is if there is a way to do it in one query, may be with Aggregate.

Here's my implementation:

let orders          = await Orders.find( {} ).populate( 'payments' );
let completedOrders = orders.filter( ( order ) => order.payments.every( ( payment ) => payment.status === 'completed' ) );

    if ( completedOrders.length !== 0 ) {
        for ( let order of completedOrders ) {
             await Orders.findByIdAndUpdate( order._id, { "status": "completed" } );
        }
    }
mongoose express node.js mongodb javascript
1 Answers

You can do an update with aggregation pipeline. Use $map to create an boolean array to indicate whether payment.status is "completed". Then use $allElementsTrue with $cond to conditionally update the outer status field.

db.collection.update({},
[
  {
    "$addFields": {
      "status": {
        "$cond": {
          "if": {
            "$allElementsTrue": {
              "$map": {
                "input": "$payment.status",
                "as": "s",
                "in": {
                  $eq: [
                    "$$s",
                    "completed"
                  ]
                }
              }
            }
          },
          "then": "completed",
          "else": "$status"
        }
      }
    }
  }
])

Here is the Mongo playground for your reference.

user_147326690
Related