I have a Mongo query like this:

db.account_balances.findAndModify({
  query: { _id: '1234' },
  update: [
    {
      $set: {
        'fieldToUpdate': {
          $cond: {
            if: { 
              $and: [ 
                { $lte: ['$updatedAt', new ISODate('2024-09-24T00:00:00.000Z')] },
                { $lt: [ { $toLong: '$numericalStringField' }, 1000 ] }
                            ],
            },
            then: 15000.0,
            else: '$fieldToUpdate'
          }
        }
      }
    }
  ],
  new: true
})

So, the idea is to search by certain criteria, and update the document if only it matches another criteria. In my case, numericalStringField is a field that typed as String but actually has numerical values (a legacy code – change the data type would be another story).

The criteria to update the record is: if incoming value for the numerical string field is bigger. That’s why I try to cast it to Long to make it work. Directly comparing String to String is not appropriate since it would be comparing lexically.

Is this something that can be achieved using Spring Data Mongo API? Tried this before but no luck:

val criteriaList = val criteriaList = mutableListOf(
  Criteria.where(PropertyName.lastUpdated).lte(eventDate),
)

lastNumericalId?.toLongOrNull()?.let {
  val c2 = Criteria.expr {
    ComparisonOperators.Lt.valueOf(
      ConvertOperators.ToLong.toLong("numericalStringField")
    ).lessThan(it)
  }
  
  criteriaList.add(c2)
}

val condition = Criteria().andOperator(*criteriaList.toTypedArray())

val update = AggregationUpdate.update()
val setOperation = SetOperation(
  "fieldToUpdate",
  ConditionalOperators
    .`when`(condition)
    .then(value)
    .otherwiseValueOf(propertyName),
)
update.set(setOperation)

mongoTemplate.findAndModify(
  Query.query(Criteria.where("_id").`is`("1234"),
  update,
  FindAndModifyOptions.options().upsert(true).returnNew(true),
  TheCollectionDto::class.java,
)