Mongo查询用于比较两个索引字段

Is there any alternative for this mongo query

这个mongo查询有没有其他选择

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.

因为此查询扫描所有记录而不利用索引。

3 个解决方案

#1

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.

我建议你应该在你的文档中创建一个额外的字段，它是field2和field1之间的区别，然后在其上创建一个索引。

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

在findAndModify（）查询中增加field2（从而增加差异），您也应该增加该差异字段：

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

#2

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).

由于$ where将在JavaScript中加载和eval并且不会使用任何索引，因此最好的替代方法是在包含布尔结果的文档模式中创建一个额外的布尔字段，例如“isField2GreaterThanField1”（field1 ）。

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

要创建额外字段，您必须从find（）操作迭代结果并更新循环中的集合：

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:

如果处理大型集合，可能会损害上述更新操作的性能，但是使用Bulk API可以通过减少发送到服务器的更新操作量来简化更新以实现最大效率，每1000个排队操作发送一次：

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

#3

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.

根据文档，$ where运算符要求数据库处理集合中每个文档的JavaScript表达式或函数。这意味着表达式“this.field1 ”被视为javascript表达式，因此不使用索引。

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:

您不能使用find查询本地（不使用JavaScript）比较MongoDB中的两个字段，但是，您可以使用聚合框架来实现此目的。通过使用$ match运算符，您可以执行类似于以下代码段的操作：

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

#1