CODEWITHSUNDEEP
mongodb
Brian Singer
Brian Singer

Reputation: 62

Mongodb query selecting using document's field

I have the query:

.find({$where: 'this.lastEmailDate < Date.now() - this.warnTime'})

This is a very slow operation. Is there a way to convert this query to use $lt?

I have seen that some people have suggested creating a new variable and selecting using that, but the issue is that Date.now() is constantly changing.

Upvotes: 1

Views: 92

Answers (1)

chridam
chridam

Reputation: 103365

Indeed the operation is slow because of the $where operator which has pretty bad performance issues. The reason being that when you perform a normal (non-$where) query, your client converts the query to BSON and sends that to the db and since MongoDB stored its data as BSON, the queries would be faster as it esssentially compares the query directly against the data.

Now with a $where operator query, MongoDB first creates a JavaScript object for every document in collection, then parses the documents' BSON and adds all the fields to the JS objects. It will then execute the JS you sent against the documents and then tears it all down again - this is very resource-intensive operation hence the slow queries with the $where clause.

Now, the best approach would be to leverage the query with the aggregation framework. Consider the following pipeline which does exactly the same query but much faster and thus better performance:

db.collection.aggregate([
    { 
        "$redact": { 
            "$cond": [
                { 
                    "$eq": [ 
                        { "$comp": [ 
                            "$lastEmailDate", 
                             { "$subtract": [new Date(), "$warnTime"] }
                        ]}, 
                        -1 
                    ] 
                },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

The above uses the $redact operator which essentially restricts the contents of the documents based on information stored in the documents themselves.

Depending on the expression that it feeds into, it resolves to query the collection by evaluates the fields using the $cond logical operator. Within the $cond operator, you can use the $eq an the $cmp with the $subtract operators do carry out the logic:

If the result of the $cmp operation is -1, then display return the whole document (at document level) else ) The $cmp expression will evaluate to -1 if lastEmailDate is less that the result when the warnTime is subtracted from the current date

Upvotes: 1

Related Questions