CODEWITHSUNDEEP
pythonmongodbmongoengine
nosheyakku
nosheyakku

Reputation: 346

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?

Upvotes: 0

Views: 103

Answers (2)

rohanraj
rohanraj

Reputation: 402

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" })

Upvotes: 2

hhharsha36
hhharsha36

Reputation: 3349

You have to use aggregation instead of the find command since self-referencing of documents in addition to arithmetic operations won't work on it.

Below is the aggregation command you are looking for. Convert it into motoengine equivalent command.

db.collection.aggregate([
  {
    "$match": {
      "$expr": {
        "$gte": [
          "$targetAmount",
          {
            "$sum": [
              "$totalAmount",
              10
            ]
          },
        ],
      },
    },
  },
  {
    "$sort": {
      "highPriority": -1,
      "lastTimeUsed": 1,
    },
  },
])

Mongo Playground Sample Execution

Upvotes: 0

Related Questions