MongoDB field increment with max condition in update statement

Question

For MongoDB I'm looking for atomic update that will increment field and if that increment will exceeds maximum given number it will stat that maximum number. Same behavior can be achieved with combination of $inc and $min operators but sadly not in one atomic update. Look at example below.

Example document

{
    "_id": 1,
    "i": 0
}

Queries

db.test.update({_id:1}, {$set:{"a":"foo"}, $inc:{i:1}}); db.test.update({_id:1}, {$min:{i:2}});
db.test.update({_id:1}, {$set:{"b":"bar"}, $inc:{i:1}}); db.test.update({_id:1}, {$min:{i:2}});
db.test.update({_id:1}, {$set:{"c":"baz"}, $inc:{i:1}}); db.test.update({_id:1}, {$min:{i:2}});

Result document

{
    "_id": 1,
    "i": 2,
    "a": "foo",
    "b": "bar",
    "c": "baz"
}

Update

Thanks to Christian P answer I realized that I forgot to mention one more condition. I need document to be updated because I need update more fields than is shown in example. In fact I need increment ceiling (maximum) condition in update statement. I've updated my example to make this clear.

score 6 · Answer 1 · answered Jun 18 '14 at 08:18

6

You're looking for a findAndModify command:

db.test.findAndModify({
    query: { _id: 1, i: { $lt: 2 } },
    update: { $inc: { i: 1 } }
})

This query will update the document only if i is not greater than your maximum given value..

answered Jun 18 '14 at 08:18

Christian P

12,032
6
60
71

4

Thanks for your answer you're right. But I forget to explain in question that I actually need update more fields with query so I can't just exclude any document from update see my question update. – michal.kreuzman Jun 18 '14 at 11:39

score 0 · Answer 2 · answered May 20 '22 at 00:55

Very old question, but I absolutely needed this and I was surprised that the atomic solution was not posted.

You have to use a Transaction, which essentially creates a virtual session where you can atomically read/write to the database with whatever logic you desire. At the end, you commit the transaction and all the changes you made, or abort it if anything wasn't correct.

Here's a solution using Node.js with async/await (Node.js Transaction Example)

const session = client.startSession()
try {

    // Begin transaction
    session.startTransaction({
        readConcern: { level: "majority" },
        readPreference: "primary",
        writeConcern: { w: "majority" },
        maxCommitTimeMS: 1000
    })
    let test = client.db('testdb').collection('test')

    // Run your operations in order, always passing the session
    await test.updateOne({ _id: 1 }, { $set: { "a": "foo" } }, {session})

    // Increment if less than 2
    await test.updateOne({ _id: 1, i: { $lt: 2 } }, { $inc: { i: 1 } }, {session})

    // You can do more operations here, reads, writes, etc.

    // If you want to abort the entire transaction, and not commit any of your
    // work, you can throw an error.

    // Commit both operations atomically
    await session.commitTransaction()

} catch (error) {
    // Handle error
    await session.abortTransaction()
} finally {
    await session.endSession()
}

The result is that both writes are effectively performed in a single operation. In my case, I needed to update multiple fields with different values, only updating those fields if they would not result in a negative value. If any fields could not be updated, it would inform the user of the unchanged fields. This means I had to use multiple writes with the $gte query, however all fields had to be updated atomically. Using a transaction was the solution for me.

I hope this is the solution that works for those with similar problems.

score 0 · Answer 3 · answered May 20 '22 at 01:04

With MongoDB v4.2+, you can simply leverage update with aggregation pipeline. You can simply $set the field to be $min of the cap and the incremented value.

db.collection.update({
  _id: 1
},
[
  {
    $set: {
      "i": {
        $min: [
          {
            $add: [
              "$i",
              1
            ]
          },
          2
        ]
      },
      "a": "foo",
      "b": "bar",
      "c": "baz"
    }
  }
],
{
  multi: true
})

Here is the Mongo Playground for your reference.

MongoDB field increment with max condition in update statement

3 Answers3

Linked