MongoDb查询条件：比较两个字段

Question

我有一个包含两个字段Grade1和Grade2的集合T，我想选择那些满足条件Grade1 > Grade2的记录，如何在MySQL中编写查询语句？

Select * from T Where Grade1 > Grade2

- Diego Cheng

Answer 1

您可以使用$where。 只需注意它会相对较慢（必须在每条记录上执行Javascript代码），因此如果可以，请与索引查询结合使用。

db.T.find( { $where: function() { return this.Grade1 > this.Grade2 } } );

更加简洁的写法：

db.T.find( { $where : "this.Grade1 > this.Grade2" } );

对于mongodb v.3.6+的更新

你可以使用$expr，如最近的回答所述。

- Ian

---

哎呀，我明白了，只需要联合使用JavaScript或其他Shell脚本，谢谢你们两个！ - Diego Cheng

你也可以更加简洁地写成这样... > db.T.find({ $where : "this.Grade1 > this.Grade2" }); 这条命令的意思是在集合T中查找满足Grade1大于Grade2的文档。 - Justin Jenkins

我该如何使用 $where: function() { return this.Grade1 - this.Grade2 > variable } ？ - Luis González

当我尝试运行 db.T.find({$where: function() {return this.startDate == ISODate("2017-01-20T10:55:08.000Z");}}); 时，它没有返回任何结果，即使集合中的一个文档是 ISODate("2017-01-20T10:55:08.000Z")。但是 <= 和 >= 看起来是有效的。有什么想法吗？ - cateyes

@cateyes 或许有点晚了...但当使用==比较两个日期时，纯javascript总是返回false。然而，在Mongo中，你可以在查询中搜索日期的完全匹配项。其中一个解决方法是使用.getTime ()将其转换为毫秒或其他单位：this.startDate.getTime() == ISODate("2017-01-20T10:55:08.000Z").getTime()。 - leinaD_natipaC

Answer 2

您可以使用$expr（3.6版本的mongo运算符）在常规查询中使用聚合函数。

比较查询运算符和聚合比较运算符。

常规查询：

db.T.find({$expr:{$gt:["$Grade1", "$Grade2"]}})

聚合查询：

db.T.aggregate({$match:{$expr:{$gt:["$Grade1", "$Grade2"]}}})

- s7vr

---

使用$expr比$where有什么好处吗？ - Akaisteph7

@Akaisteph7 $expr 是首选的运算符。请查看 documentation of $where，它列出了许多限制，并且比 $expr 更慢。 - Wernfried Domscheit

Answer 3

如果您的查询仅包含$where操作符，则只需传递JavaScript表达式：

db.T.find("this.Grade1 > this.Grade2");

为了提高性能，运行一个聚合操作，其中包含一个 $redact pipeline，以过滤满足给定条件的文档。

$redact pipeline 结合了 $project 和 $match 的功能，实现字段级别的遮蔽。它将使用 $$KEEP 返回所有符合条件的文档，并使用 $$PRUNE 变量从 pipeline 结果中删除不符合条件的文档。

---

运行以下聚合操作可以更有效地过滤文档，而不是在大集合中使用$where，因为它使用单个 pipeline 和本地 MongoDB 运算符，而不是 JavaScript 评估和 $where，这可能会减慢查询速度：

db.T.aggregate([
    {
        "$redact": {
            "$cond": [
                { "$gt": [ "$Grade1", "$Grade2" ] },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

这是将两个管道 $project 和 $match 合并的更简化版本：

db.T.aggregate([
    {
        "$project": {
            "isGrade1Greater": { "$cmp": [ "$Grade1", "$Grade2" ] },
            "Grade1": 1,
            "Grade2": 1,
            "OtherFields": 1,
            ...
        }
    },
    { "$match": { "isGrade1Greater": 1 } }
])

使用 MongoDB 3.4 或更高版本：

db.T.aggregate([
    {
        "$addFields": {
            "isGrade1Greater": { "$cmp": [ "$Grade1", "$Grade2" ] }
        }
    },
    { "$match": { "isGrade1Greater": 1 } }
])

- chridam

---

最后一个好像对我无效。isGrade1Greater字段已经正确添加和评估，但由于某些原因查询匹配所有行，无论isGrade1Greater的值如何。这种行为的原因是什么？编辑：没事了，我没有将数组传递给aggregate()，而是将每个聚合作为参数本身传递，漏了这个。 - ThatBrianDude

Answer 4

如果性能比可读性更重要，且条件仅由简单的算术运算组成，则可以使用聚合管道。首先，使用$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)

使用聚合函数：

%timeit -n1 -r1 list(coll.aggregate([
    {
        '$project': {
            'diff': {'$subtract': ['$Grade1', '$Grade2']},
            'Grade1': 1,
            'Grade2': 1
        }
    },
    {
        '$match': {'diff': {'$gt': 0}}
    }
]))

1 次循环，最佳循环时间为 192 毫秒每次循环

使用 find 和 $where：

%timeit -n1 -r1 list(coll.find({'$where': 'this.Grade1 > this.Grade2'}))

1次循环，1次最佳结果：每次循环4.54秒

- Sina