0

Here I have two documents inside test.test collection:

> db.test.find()
{ "_id" : ObjectId("AAA"), "last_updated" : 1000, "update_interval" : 50 }
{ "_id" : ObjectId("BBB"), "last_updated" : 1200, "update_interval" : 50 }

I want to find documents that should be updated(by testing last_updated <= now - update_interval expression).

First, I tried following in Python with pymongo:

from pymongo import MongoClient

db = MongoClient().test

now = 1225
r = db.test.find({
    'last_updated': {'$lte': {'$subtract': [now, '$update_interval']}}
})
for item in r:
    print(r)

However, it doesn't print anything. But when I change {'$subtract': [now, '$update_interval']} to hardcoded value 1175, it works properly.

I thought that I can't use $subtract outside of aggregation pipelines, so I tried another:

r = db.test.aggregate([
    {
        '$match': {
            'last_updated': {'$lte': {'$subtract': [now, '$update_interval']}},
        },
    },
])

Um, it just looks identical to previous one, and yes it didn't work too.

Finally, I gave last try:

r = db.test.aggregate([
    {
        '$project': {
            'when_to_update': {'$subtract': [now, '$update_interval']},
            'last_updated': True,
            'update_interval': True,
        },
    },
    {
        '$match': {
            'last_updated': {'$lte': '$when_to_update'},
        },
    },
])

Ok, it doesn't work again. But the weird thing is, when I passed just $project pipeline, the result looks like:

{'_id': ObjectId('AAA'), 'last_updated': 1000.0, 'update_interval': 50.0, 'when_to_update': 1175.0}
{'_id': ObjectId('BBB'), 'last_updated': 1200.0, 'update_interval': 50.0, 'when_to_update': 1175.0}

So it should work after passing $match pipeline too! Am I missing something? Waiting for your answers, thanks.

hallazzang
  • 651
  • 8
  • 18
  • 1
    You cant compare fields in match stage. It is possible from 3.6 version. The easy fix would be to change `'can_update': {'$lte': ['$last_updated', {'$subtract': [now, '$update_interval']}]}` and `{ '$match': { 'can_update': true, }, }` – s7vr Oct 09 '17 at 14:52
  • Possible duplicate of [MongoDb query condition on comparing 2 fields](https://stackoverflow.com/questions/4442453/mongodb-query-condition-on-comparing-2-fields) – s7vr Oct 09 '17 at 14:53

1 Answers1

1

Right, you can't use $subtract outside of aggregation pipelines. The aggregation projection operators are unique to the aggregation framework.

At the moment, using fields' values in a $match expression is tricky; in MongoDB 3.6 the new $expr operator will help. Meanwhile try this:

now = 1225
r = db.test.aggregate([
    {
        '$project': {
            'comparison': {'$cmp': [
                now,
                [{'$sum': ['$update_interval', '$last_updated']}]]},
            'last_updated': True,
            'update_interval': True
        }
    }, {
        '$match': {
            'comparison': -1
        }
    }
])

for item in r:
    print(item)

In your for-loop you must print "item", not "r".

A. Jesse Jiryu Davis
  • 23,641
  • 4
  • 57
  • 70
  • Thank you for a nice suggestion. BTW I've found a way myself by doing `r = db.test.aggregate([{'$addFields': {'when_to_update': {'$add': ['$last_refreshed', '$interval']}}}, {'$match': {'when_to_update': {'$lte': now}}}])`. And, I'd like to update my mongodb server to 3.6 now... – hallazzang Oct 10 '17 at 01:50
  • Great. MongoDB 3.6 will be out in a couple months, meanwhile you can download the 3.5.x development versions: https://www.mongodb.com/download-center#development – A. Jesse Jiryu Davis Oct 10 '17 at 12:30