Mongodb - Perform calculation with a just deleted value in aggregation pipeline-CodePudding

I have this document:

{
  _id: ObjectId('asdu7329n'),
  payments: [
    { _id: ObjectId('28sdf310'), paidAmount: 20 },
    { _id: ObjectId('2fsd410'), paidAmount: 15 },
    { _id: ObjectId('2fs32gd70'), paidAmount: 35 },
  ],
  totalPaidAmount: 70
}

What I want is to re-calculate the totalPaidAmount field when a payment is removed, right now I'm deleting the payment in this way:

const query = { _id: ObjectId(saleId) };
const update = [
  { $set: { payments: { $filter: {
    input: '$payments',
    cond: {
      $ne: [ "$$this._id", ObjectId(/* paymentId to delete */) ]
    }
  }}}}
]

await salesSchema.findOneAndUpdate(query, update);

I know that I have to use $subtract possibly in a second $set stage but how could I reference the paidAmount value from the object so that I can do something like this:

{
  $set: {
    totalPaidAmount: {
      $subtract: [ '$totalPaidAmount', /* paidAmount value for the deleted payment */ ] 
    }
  }
}

I know that I can just sum the paidAmount values for all the indexes of payments but what if there is like 1000 or more items? even if it doesn't hit the performance too much it seems to me more logical to take advantage of the totalPaidAmount field here.

CodePudding user response：

If you want to subtract you can use the $filter:

db.collection.update(
  {payments: {$elemMatch: {_id: ObjectId("63920f965d15e98e3d7c452c")}}},
  [{$project: {
      payments: {
        $filter: {
          input: "$payments",
          cond: {$ne: ["$$this._id", ObjectId("63920f965d15e98e3d7c452c")]}
        }
      },
      totalPaidAmount: {
        $subtract: [
          "$totalPaidAmount",
          {$getField: {
              input: {
                $first: {
                  $filter: {
                    input: "$payments",
                    cond: {$eq: ["$$this._id", ObjectId("63920f965d15e98e3d7c452c")]}
                  }
                }
              },
              field: "paidAmount"
            }
          }
        ]
      }
  }}
])

See how it works on the playground example

But I would go with the good old $sum:

db.collection.update(
  {payments: {$elemMatch: {_id: ObjectId("63920f965d15e98e3d7c452c")}}},
  [{$project: {
      payments: {
        $filter: {
          input: "$payments",
          cond: {$ne: ["$$this._id", ObjectId("63920f965d15e98e3d7c452c")]}
        }
      }
  }},
  {$set: {totalPaidAmount: {$sum: "$payments.paidAmount"}}}
])

See how it works on the playground example