CODEWITHSUNDEEP
mongodbindexingmongodb-query
kamesh
kamesh

Reputation: 421

Mongo query for comparing two indexed fields

Is there any alternative for this mongo query 

db.test.findAndModify(
{
   query:{$where:"this.field1<this.field2"},
   update:{$inc:{field2:1}},
   sort:{field2:1}
});

because this query scanning all records and not taking the advantage of indexing.

Upvotes: 0

Views: 951

Answers (3)

Alnitak
Alnitak

Reputation: 339985

I suggest you should create an additional field in your documents that is the difference between field2 and field1, and then create an index on that.

In your findAndModify() query that increments field2 (thereby increasing the difference) you should increment that difference field too:

db.test.findAndModify(
{
    query: {difference: {$gt: 0}},
    update: {$inc: {field2: 1, difference: 1}}
});

Upvotes: 1

Alex
Alex

Reputation: 21766

According to the documentation, the $where operator requires that the database processes the JavaScript expression or function for each document in the collection. This means that the expression "this.field1<this.field2" is treated as a JavaScript expression and therefore the the indices are not used.

You can't natively (without JavaScript) compare two fields in MongoDB using the find query, however, you can use the aggregation framework for this purpose. By using the $match operator you could do something similar to the snippet below:

db.data.aggregate([{
   $project : {
        equal : {
           $eq : ["$field1", "$field2"]  
       },
       doc : "$$ROOT"   // store the whole document, this is optional
   }
}, {
    $match : {
       equal : true   
    }
}]);

Upvotes: 0

chridam
chridam

Reputation: 103445

As $where will load and eval in JavaScript and won't use any indexes, your best alternative would be to create an extra boolean field, say "isField2GreaterThanField1", in your document schema that holds the boolean result (field1 < field2).

To create the extra field, you would have to iterate the results from a find() operation and update the collection within the loop:

db.test.find({}).forEach(function(doc) { 
    var comparison = (doc.field1 < doc.field2);
    db.test.update(
        { "_id": doc._id }, 
        {
            "$set": { 
                "isField2GreaterThanField1": comparison  
            }
        }
    );
});

Perfomance with the above update operation can be compromised if dealing with large collections, however using the Bulk API can streamline the updates for maximised efficiency by reducing the amount of update operations sent to the sever, sending once every 1000 queued operations:

var bulk = db.test.initializeOrderedBulkOp(),   
    counter = 0;

db.test.find({}).forEach(function(doc) { 
    var comparison = (doc.field1 < doc.field2);    
    bulk.find({ "_id": doc._id }).updateOne({
        "$set": { 
            "isField2GreaterThanField1": comparison 
        }
    });

    counter++;
    if (counter % 1000 == 0) {
        // Execute per 1000 operations and re-initialize every 1000 update statements
        bulk.execute();
        bulk = db.test.initializeOrderedBulkOp();
    }
});

// Clean up queues
if (counter % 1000 != 0){
    bulk.execute();
}

Having created the new field, you can then create an index on it and query your documents as in

db.test.find({"isField2GreaterThanField1": true});

Upvotes: 0

Related Questions