database 你如何在 Mongo 中查询“不为空”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4057196/
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
How do you query for "is not null" in Mongo?
提问by TIMEX
I would like to execute a following query:
我想执行以下查询：
db.mycollection.find(HAS IMAGE URL)
What should be the correct syntax?
正确的语法应该是什么？
回答by Tim Gautier
This will return all documents with a key called "IMAGE URL", but they may still have a null value.
这将返回具有名为“IMAGE URL”的键的所有文档，但它们可能仍具有空值。
db.mycollection.find({"IMAGE URL":{$exists:true}});
This will return all documents with both a key called "IMAGE URL" anda non-null value.
这将返回具有名为“IMAGE URL”的键和非空值的所有文档。
db.mycollection.find({"IMAGE URL":{$ne:null}});
Also, according to the docs, $exists currently can't use an index, but $ne can.
此外，根据文档， $exists 当前不能使用索引，但 $ne 可以。
Edit: Adding some examples due to interest in this answer
编辑：由于对此答案感兴趣而添加了一些示例
Given these inserts:
鉴于这些插入：
db.test.insert({"num":1, "check":"check value"});
db.test.insert({"num":2, "check":null});
db.test.insert({"num":3});
This will return all three documents:
这将返回所有三个文件：
db.test.find();
This will return the first and second documents only:
这将仅返回第一个和第二个文档：
db.test.find({"check":{$exists:true}});
This will return the first document only:
这将仅返回第一个文档：
db.test.find({"check":{$ne:null}});
This will return the second and third documents only:
这将仅返回第二个和第三个文档：
db.test.find({"check":null})
回答by Amitesh
One liner is the best :
一个班轮是最好的：
db.mycollection.find({ 'fieldname' : { $exists: true, $ne: null } });
Here,
这里，
mycollection: place your desired collection name
mycollection：放置您想要的集合名称
fieldname: place your desired field name
fieldname：放置您想要的字段名称
Explaination :
说明：
$exists: When is true, $exists matches the documents that contain the field, including documents where the field value is null. If is false, the query returns only the documents that do not contain the field.
$exists：当为真时，$exists 匹配包含该字段的文档，包括字段值为空的文档。如果为 false，则查询仅返回不包含该字段的文档。
$neselects the documents where the value of the field is not equal to the specified value. This includes documents that do not contain the field.
$ne选择字段值不等于指定值的文档。这包括不包含该字段的文档。
So in your provided case following query going to return all the documents with imageurl field exists and having not null value:
因此，在您提供的情况下，以下查询将返回所有具有 imageurl 字段且不为空值的文档：
db.mycollection.find({ 'imageurl' : { $exists: true, $ne: null } });
回答by user2293072
In pymongo you can use:
在 pymongo 中，您可以使用：
db.mycollection.find({"IMAGE URL":{"$ne":None}});
Because pymongo represents mongo "null" as python "None".
因为 pymongo 将 mongo "null" 表示为 python "None"。
回答by Farouk El kholy
db.collection_name.find({"filed_name":{$exists:true}});
fetch documents that contain this filed_name even it is null.
获取包含此文件名的文档，即使它为空。
My proposition:
我的提议：
db.collection_name.find({"field_name":{$type:2}}) //type:2 == String
You can check on the required attribute's type, it will return all the documents that its field_name queried contains a value because you are checking on the filed's type else if it is null the type condition doesn't match so nothing will be returned.
您可以检查所需属性的类型，它将返回其 field_name 查询包含值的所有文档，因为您正在检查归档的类型，否则如果它为空，则类型条件不匹配，因此不会返回任何内容。
N.b: if the field_name has an empty string which means "", it will be returned.It is the same behavior for
注意：如果 field_name 有一个空字符串，表示“”，它将被返回。这是相同的行为
db.collection_name.find({"filed_name":{$ne:null}});
Extra validation:
额外验证：
Okay, so we are not finished yet we need an extra condition.
好的，所以我们还没有完成，我们需要一个额外的条件。
db.collection_name.
find({ "field_name":{$type:2},$where:"this.field_name.length >0"})
OR
或者
db.collection_name.
find({ "field_name":{$ne:null},$where:"this.field_name.length >0"})
回答by xameeramir
Sharing for future readers.
分享给未来的读者。
This query worked for us (query executed from MongoDB compass):
这个查询对我们有用（从MongoDB compass执行的查询）：
{
"fieldName": {
"$nin": [
"",
null
]
}
}
回答by Berhanu Tarekegn
db.<collectionName>.find({"IMAGE URL":{"$exists":"true"}, "IMAGE URL": {$ne: null}})
回答by Adam Comerford
An alternative that has not been mentioned, but that may be a more efficient option for some (won't work with NULL entries) is to use a sparse index(entries in the index only exist when there is something in the field). Here is a sample data set:
尚未提及的另一种替代方法，但对于某些（不适用于 NULL 条目）来说可能是更有效的选择是使用稀疏索引（索引中的条目仅在字段中存在某些内容时才存在）。这是一个示例数据集：
db.foo.find()
{ "_id" : ObjectId("544540b31b5cf91c4893eb94"), "imageUrl" : "http://example.com/foo.jpg" }
{ "_id" : ObjectId("544540ba1b5cf91c4893eb95"), "imageUrl" : "http://example.com/bar.jpg" }
{ "_id" : ObjectId("544540c51b5cf91c4893eb96"), "imageUrl" : "http://example.com/foo.png" }
{ "_id" : ObjectId("544540c91b5cf91c4893eb97"), "imageUrl" : "http://example.com/bar.png" }
{ "_id" : ObjectId("544540ed1b5cf91c4893eb98"), "otherField" : 1 }
{ "_id" : ObjectId("544540f11b5cf91c4893eb99"), "otherField" : 2 }
Now, create the sparse index on imageUrl field:
现在，在 imageUrl 字段上创建稀疏索引：
db.foo.ensureIndex( { "imageUrl": 1 }, { sparse: true } )
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
Now, there is always a chance (and in particular with a small data set like my sample) that rather than using an index, MongoDB will use a table scan, even for a potential covered index query. As it turns out that gives me an easy way to illustrate the difference here:
现在，总是有机会（特别是对于像我的示例这样的小数据集），MongoDB 将使用表扫描，而不是使用索引，即使对于潜在的覆盖索引查询也是如此。事实证明，这给了我一个简单的方法来说明这里的差异：
db.foo.find({}, {_id : 0, imageUrl : 1})
{ "imageUrl" : "http://example.com/foo.jpg" }
{ "imageUrl" : "http://example.com/bar.jpg" }
{ "imageUrl" : "http://example.com/foo.png" }
{ "imageUrl" : "http://example.com/bar.png" }
{ }
{ }
OK, so the extra documents with no imageUrlare being returned, just empty, not what we wanted. Just to confirm why, do an explain:
好的，所以没有imageUrl返回额外的文件，只是空的，不是我们想要的。只是为了确认原因，做一个解释：
db.foo.find({}, {_id : 0, imageUrl : 1}).explain()
{
"cursor" : "BasicCursor",
"isMultiKey" : false,
"n" : 6,
"nscannedObjects" : 6,
"nscanned" : 6,
"nscannedObjectsAllPlans" : 6,
"nscannedAllPlans" : 6,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"server" : "localhost:31100",
"filterSet" : false
}
So, yes, a BasicCursorequals a table scan, it did not use the index. Let's force the query to use our sparse index with a hint():
所以，是的，aBasicCursor等于表扫描，它没有使用索引。让我们强制查询使用我们的稀疏索引hint()：
db.foo.find({}, {_id : 0, imageUrl : 1}).hint({imageUrl : 1})
{ "imageUrl" : "http://example.com/bar.jpg" }
{ "imageUrl" : "http://example.com/bar.png" }
{ "imageUrl" : "http://example.com/foo.jpg" }
{ "imageUrl" : "http://example.com/foo.png" }
And there is the result we were looking for - only documents with the field populated are returned. This also only uses the index (i.e. it is a covered index query), so only the index needs to be in memory to return the results.
这就是我们正在寻找的结果——只返回填充了该字段的文档。这也只使用索引（即它是一个覆盖索引查询），所以只有索引需要在内存中才能返回结果。
This is a specialized use case and can't be used generally (see other answers for those options). In particular it should be noted that as things stand you cannot use count()in this way (for my example it will return 6 not 4), so please only use when appropriate.
这是一个专门的用例，不能普遍使用（有关这些选项，请参阅其他答案）。特别要注意的是，目前情况下您不能count()以这种方式使用（对于我的示例，它将返回 6 而不是 4），因此请仅在适当的时候使用。
回答by Taha Hamedani
The simplest way to check the existence of the column in mongo compass is :
检查 mongo compass 中列是否存在的最简单方法是：
{ 'column_name': { $exists: true } }
回答by Ankit Marothi
In an ideal case, you would like to test for all three values, null, ""or empty(field doesn't exist in the record)
在理想情况下，您希望测试所有三个值，null、""或空（记录中不存在字段）
You can do the following.
您可以执行以下操作。
db.users.find({$and: [{"name" : {$nin: ["", null]}}, {"name" : {$exists: true}}]})
回答by Sagar Varpe
the Query Will be
查询将是
db.mycollection.find({"IMAGE URL":{"$exists":"true"}})
it will return all documents having "IMAGE URL" as a key ...........
它将返回所有以“IMAGE URL”为键的文档.......
