Update document using $pull and $set all together

[Benny_Kachanovsky1 (Benny Kachanovsky)]

Hi, updateMany and updateOne accepts a limited aggregation pipeline.
We need to remove an item from an array (e.g via $pull) and update a property based on the result.

db.bills.insertMany([
  { 
    _id: "111",
    balance: 40000,
    payments: [ 
      { billPaymentId: "123",  amount: 25000  }, 
      { billPaymentId: "456", amount: 15000 } ],
  },
  { 
    _id: "222",
    balance: 12,
    payments: [ 
       { billPaymentId: "99",  amount: 3  }, 
       { billPaymentId: "101", amount: 9 } ],
  }
])

We are trying to remove some element from payments and update balance with the sum of the payments that are left.
If $pull would have worked with updateOne it would look like this:

db.bills.updateOne(
{ _id: "606f028d-9409-4f67-bd5d-f254c258ff0c" }, [
    { $pull: { billPaymentId: "427066e5-5a4a-4667-8f86-c10cb2560c77" } },
    { $set: { balance: { $sum: '$payments.amount' } } }
])

but as mentioned, $pull is not supported and this query return Unrecognized pipeline stage name: '$pull'.
Any other ideas maybe?

[Doug_Duncan]

Hi @Benny_Kachanovsky1 and welcome to the MongoDB Community forums!

As you noticed, the pipeline stages available to updateOne are limited to only the following:

Update with Aggregation Pipeline

Starting in MongoDB 4.2, the db.collection.updateOne() can use an aggregation pipeline for the update. The pipeline can consist of the following stages:

• $addFields and its alias $set
• $project and its alias $unset
• $replaceRoot and its alias $replaceWith .

The following was just a quick test to play around with things, but it could be used as a starting point to get what you want hopefully:

db.bills.updateOne(
  {
    _id: "111"
  },
  [
    {
      $set: {
        payments: {
          $filter: {
            input: "$payments",
            as: "payment",
            cond: {
              $ne: ["$$payment.billPaymentId", "123"]
            }
          }
        }
      }
    },
    {
      $set: {
        balance: {
          "$sum": "$payments.amount"
        }
      }
    }
  ]
)

I am not sure why I had to put the update for the balance into a second $set block, but it was not working in the original location for some reason.