mongodb 查询布尔字段为“不正确”（例如，错误或不存在）

Query for boolean field as "not true" (e.g. either false or non existent)

提问by Eran Medan

I'm sure I'm missing something very basic in MongoDB queries, can't seem to get this simple condition.

我确定我在 MongoDB 查询中遗漏了一些非常基本的东西，似乎无法得到这个简单的条件。

Consider this collection

考虑这个集合

> db.tests.find()
{ "_id" : ObjectId("..."), "name" : "Test1" , "deleted" : true}
{ "_id" : ObjectId("..."), "name" : "Test2" , "deleted" : false}
{ "_id" : ObjectId("..."), "name" : "Test3" }

I would simply like to query all the items that are "not deleted"

我只想查询所有“未删除”的项目

I know how to find the item that has a "deleted" flag set to true:

我知道如何找到“已删除”标志设置为 true 的项目：

> db.tests.find({deleted:true})
{ "_id" : ObjectId("..."), "name" : "Test1" , "deleted" : true}

But how do I find all items that are NOT "deleted"(e.g. negate the above query, or in other words, any items that either doesn't have a "deleted"field, or have it with value false

但是我如何找到所有不是的项目"deleted"（例如否定上述查询，或者换句话说，任何没有"deleted"字段或具有值的项目false

What I tried by guessing(please don't laugh...)

我的猜测是什么（请不要笑......）

> db.tests.find({$not : {deleted: true}})

(returns no results)

（不返回结果）

> db.tests.find({$not : {$eq:{deleted:true}}})

error: { "$err" : "invalid operator: $eq", "code" : 10068 }

错误：{“$err”：“无效运算符：$eq”，“代码”：10068}

> db.tests.find({deleted:{$not: true}})

error: { "$err" : "invalid use of $not", "code" : 13041 }

错误：{“$err”：“$not 的无效使用”，“代码”：13041 }

> db.tests.find({deleted:{$not: {$eq:true}}})

error: { "$err" : "invalid use of $not", "code" : 13034 }

错误：{“$err”：“$not 的无效使用”，“代码”：13034 }

What am I missing?

我错过了什么？

回答by Sergio Tulentsev

db.tests.find({deleted: {$ne: true}})

Where $nestands for "not equal". (Documentation on mongodb operators)

哪里$ne代表“不相等”。（有关 mongodb 运算符的文档）

回答by JohnnyHK

For the sake of completeness, another way to do this is with $in:

为了完整起见，另一种方法是使用$in：

db.test.find({deleted: {$in: [null, false]}})

Including nullin the array pulls in the docs where the deletedfield is missing. This query can use an index on {deleted: 1}in the current 2.6.6 MongoDB release.

包括null在数组中拉入deleted缺少该字段的文档。此查询可以使用{deleted: 1}当前 2.6.6 MongoDB 版本中的索引。

回答by tidwall

JohnnyHK has the best answer. The $inselector is the shortest and cleanest IMO.

JohnnyHK 给出了最好的答案。在$in选择最短，最干净的海事组织。

This will test for exactly "false" or "non existent". And can be indexed.

这将测试完全“错误”或“不存在”。并且可以被索引。

db.tests.find({$or:[{deleted:false},{deleted:{$exists:false}}]})

An example with a use of an index.

使用索引的示例。

((function(){
    print("creating collection 'testx' and inserting 50 trues, 50 falses, 50 non-existents");
    db.testx.drop();
    db.testx.ensureIndex({deleted:1});
    for (var i=0;i<50;i++){
        db.testx.insert({i:i,deleted:false});
    };
    for (var i=0;i<50;i++){
        db.testx.insert({i:i,deleted:true});
    };
    for (var i=0;i<50;i++){
        db.testx.insert({i:i});
    };
    var res0 = db.testx.find().explain();
    var res1 = db.testx.find({deleted:false}).explain();
    var res2 = db.testx.find({deleted:true}).explain();
    var res3 = db.testx.find({deleted:{$exists:false}}).explain();
    var res4 = db.testx.find({$or:[{deleted:false},{deleted:{$exists:false}}]}).explain();
    var res5 = db.testx.find({$or:[{deleted:true},{deleted:{$exists:false}}]}).explain();
    var res6 = db.testx.find({deleted:{$in:[false,null]}}).explain();
    print("res0: all objects                      ("+res0["n"]+" found, "+res0["nscannedObjects"]+" scanned)");
    print("res1: deleted is false                 ("+res1["n"]+" found, "+res1["nscannedObjects"]+" scanned)");
    print("res2: deleted is true                  ("+res2["n"]+" found, "+res2["nscannedObjects"]+" scanned)");
    print("res3: deleted is non-existent          ("+res3["n"]+" found, "+res3["nscannedObjects"]+" scanned)");
    print("res4: deleted is false or non-existent ("+res4["n"]+" found, "+res4["nscannedObjects"]+" scanned)");
    print("res5: deleted is true or non-existent  ("+res5["n"]+" found, "+res5["nscannedObjects"]+" scanned)");
    print("res6: deleted is in [false,null]       ("+res5["n"]+" found, "+res5["nscannedObjects"]+" scanned)");
})())

This should print

这应该打印

creating collection 'testx' and inserting 50 trues, 50 falses, 50 non-existents
res0: all objects                      (150 found, 150 scanned)
res1: deleted is false                 (50 found, 50 scanned)
res2: deleted is true                  (50 found, 50 scanned)
res3: deleted is non-existent          (50 found, 50 scanned)
res4: deleted is false or non-existent (100 found, 100 scanned)
res5: deleted is true or non-existent  (100 found, 100 scanned)
res6: deleted is in [false,null]       (100 found, 100 scanned)

回答by JohnPan

For the case that someone needs this in an aggregation pipeline instead of find, this is what worked for me

对于有人在聚合管道中需要它而不是 的情况find，这对我有用

db.getCollection('tests').aggregate([ 
  // ...previous operations...
  { $addFields: { "deleted_conclusion": { $cond: {
        if:{ $ne: [ "$deleted", false ]}, then: { $cond: [ "$deleted", ":TRUE", ":FALSY"]}, else: ":FALSE"
  }}}}
])

After adding the extra field you can go on with pipeline stages and have the information you miss

添加额外字段后，您可以继续进行流水线阶段并获得您错过的信息

回答by Jon Kern

In case you are looking for mongoid syntax (I am using this in a rails app), this is what I came up with for a company's users:

如果您正在寻找 mongoid 语法（我在 Rails 应用程序中使用它），这就是我为公司用户提出的：

2.3.1 :042 > accepted_consent = org.users.active.where(:accepted_terms_and_conditions => true).count
 => 553 
2.3.1 :043 > not_accepted_yet = org.users.active.where(:accepted_terms_and_conditions.ne => true).count
 => 6331 
2.3.1 :044 > 6331+553
 => 6884 
2.3.1 :045 > org.users.active.count
 => 6884