Search code examples
pythonmongodbmongoengine

How to use the sum of two fields when searching for a document in MongoDB?

I have a collection of accounts and I am trying to find an account in which the targetAmount >= totalAmount + N

{
  "_id": {
    "$oid": "60d097b761484f6ad65b5305"
  },
  "targetAmount": 100,
  "totalAmount": 0,
  "highPriority": false,
  "lastTimeUsed": 1624283088
}

Now I just select all accounts, iterate over them and check if the condition is met. But I'm trying to do this all in a query:

amount = 10

tasks = ProviderAccountTaskModel.objects(
    __raw__={
        'targetAmount': {
            '$gte': {'$add': ['totalAmount', amount]}
        }
    }
).order_by('-highPriority', 'lastTimeUsed')

I have also tried using the $sum, but both options do not work.
Can't it be used when searching, or am I just going the wrong way?

Solution

  • You can use a $where. Just be aware it will be fairly slow (has to execute Javascript code on every record) so combine with indexed queries if you can.

    db.getCollection('YourCollectionName').find( { $where: function() { return this.targetAmount > (this.totalAmount + 10) } })

    or more compact way of doing it will be

    db.getCollection('YourCollectionName').find( { $where: "this.targetAmount > this.totalAmount +  10" })