MongoDB decrement until zero

user_10016741 0
Viewed 550

I would like to achieve an operation in MongoDB that would be analogous to doc.value = max(doc.value - amount, 0). I could do it by fetching document, updating its value then saving it, but is it possible with an atomic operation to avoid problems with synchronisation of parallel decrements?

mongodb-query mongodb
2 Answers

It is, in fact, possible to achieve with a single operation. All you need is an aggregation pipeline inside an update operator.

Let's say you have a doc that looks like this:

{
    "key": 1,
    value: 30
}

You want to subtract x from value and if the resulting value is less than zero, set value to 0, otherwise set it to whatever value - x is. Here is an update aggregator you need. In this example I am subtracting 20 from value.

db.collection.update({
  key: 1
},
[
  {
    $set: {
      "value": {
        $cond: [
          {
            $gt: [
              {
                $subtract: [
                  "$value",
                  20
                ]
              },
              0
            ]
          },
          {
            $subtract: [
              "$value",
              20
            ]
          },
          0
        ]
      }
    }
  }
])

The result will be:

  {
    "key": 1,
    "value": 10
  }

But if you change 20 to, say 44, the result is:

  {
    "key": 1,
    "value": 0
  }

Here is a Playground for you: https://mongoplayground.net/p/Y9yO6v9Oca8

user_19279910

Kudos to codemonkey's response for providing a solution using aggregation pipelines for an atomic transaction.

Here's a slightly simpler aggregation pipeline that takes advantage of the $max operator:

db.collection.update({},
[
  {
    $set: {
      "value": {
        $max: [
          0,
          {
            $subtract: [
              "$value",
              20
            ]
          }
        ]
      }
    }
  }
],
{
  multi: true
})

The pipeline sets the value to the maximum of 0 and the result of the decrement.

Playground

user_50999710
Related