比较 2 个字段的 MongoDb 查询条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4442453/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MongoDb query condition on comparing 2 fields
提问by Diego Cheng
I have a collection T, with 2 fields: Grade1and Grade2, and I want to select those with condition Grade1 > Grade2, how can I get a query like in MySQL?
我有一个集合T，有 2 个字段：Grade1and Grade2，我想选择那些有条件的Grade1 > Grade2，我怎样才能像在 MySQL 中一样获得查询？
Select * from T Where Grade1 > Grade2
回答by Ian
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.
您可以使用 $where。请注意它会相当慢（必须在每条记录上执行 Javascript 代码），因此如果可以，请结合索引查询。
db.T.find( { $where: function() { return this.Grade1 > this.Grade2 } } );
or more compact:
或更紧凑：
db.T.find( { $where : "this.Grade1 > this.Grade2" } );
UPD for mongodb v.3.6+
mongodb v.3.6+ 更新
you can use $expras described in recent answer
您可以$expr按照最近的回答中的描述使用
回答by Sagar Veeram
You can use $expr( 3.6 mongo version operator ) to use aggregation functions in regular query.
您可以使用$expr( 3.6 mongo version operator ) 在常规查询中使用聚合函数。
Compare query operatorsvs aggregation comparison operators.
比较query operatorsvs aggregation comparison operators。
Regular Query:
常规查询：
db.T.find({$expr:{$gt:["$Grade1", "$Grade2"]}})
Aggregation Query:
聚合查询：
db.T.aggregate({$match:{$expr:{$gt:["$Grade1", "$Grade2"]}}})
回答by chridam
If your query consists only of the $whereoperator, you can pass in just the JavaScript expression:
如果您的查询仅包含$where运算符，您可以只传入 JavaScript 表达式：
db.T.find("this.Grade1 > this.Grade2");
For greater performance, run an aggregate operation that has a $redactpipeline to filter the documents which satisfy the given condition.
为了获得更高的性能，请运行具有$redact管道的聚合操作来过滤满足给定条件的文档。
The $redactpipeline incorporates the functionality of $projectand $matchto implement field level redaction where it will return all documents matching the condition using $$KEEPand removes from the pipeline results those that don't match using the $$PRUNEvariable.
该$redact管道包含的功能$project，并$match实现现场级新版本，其中将返回使用符合条件的所有文件$$KEEP，并从管道结果那些不使用匹配$$PRUNE变量。
Running the following aggregate operation filter the documents more efficiently than using $wherefor large collections as this uses a single pipeline and native MongoDB operators, rather than JavaScript evaluations with $where, which can slow down the query:
运行以下聚合操作比使用$where大型集合更有效地过滤文档，因为它使用单个管道和本机 MongoDB 运算符，而不是使用 的 JavaScript 评估$where，这会减慢查询速度：
db.T.aggregate([
{
"$redact": {
"$cond": [
{ "$gt": [ "$Grade1", "$Grade2" ] },
"$$KEEP",
"$$PRUNE"
]
}
}
])
which is a more simplified version of incorporating the two pipelines $projectand $match:
这是合并两个管道的更简化版本，$project并且$match：
db.T.aggregate([
{
"$project": {
"isGrade1Greater": { "$cmp": [ "$Grade1", "$Grade2" ] },
"Grade1": 1,
"Grade2": 1,
"OtherFields": 1,
...
}
},
{ "$match": { "isGrade1Greater": 1 } }
])
With MongoDB 3.4and newer:
使用MongoDB 3.4及更新版本：
db.T.aggregate([
{
"$addFields": {
"isGrade1Greater": { "$cmp": [ "$Grade1", "$Grade2" ] }
}
},
{ "$match": { "isGrade1Greater": 1 } }
])
回答by Sina
In case performance is more important than readability and as long as your condition consists of simple arithmetic operations, you can use aggregation pipeline. First, use $project to calculate the left hand side of the condition (take all fields to left hand side). Then use $match to compare with a constant and filter. This way you avoid javascript execution. Below is my test in python:
如果性能比可读性更重要，并且只要您的条件由简单的算术运算组成，您就可以使用聚合管道。首先，使用 $project 计算条件的左侧（将所有字段移到左侧）。然后使用 $match 与常量和过滤器进行比较。这样你就可以避免 javascript 执行。下面是我在python中的测试：
import pymongo
from random import randrange
docs = [{'Grade1': randrange(10), 'Grade2': randrange(10)} for __ in range(100000)]
coll = pymongo.MongoClient().test_db.grades
coll.insert_many(docs)
Using aggregate:
使用聚合：
%timeit -n1 -r1 list(coll.aggregate([
{
'$project': {
'diff': {'$subtract': ['$Grade1', '$Grade2']},
'Grade1': 1,
'Grade2': 1
}
},
{
'$match': {'diff': {'$gt': 0}}
}
]))
1 loop, best of 1: 192 ms per loop
1 个循环，最好的 1 个：每个循环 192 毫秒
Using find and $where:
使用 find 和 $where：
%timeit -n1 -r1 list(coll.find({'$where': 'this.Grade1 > this.Grade2'}))
1 loop, best of 1: 4.54 s per loop
1 个循环，最好的 1 个：每个循环 4.54 秒
