具有null的MongoDB比较运算符 [英] MongoDB comparison operators with null

问题描述

在MongoDB中，我想使用$ gt和$ lt比较运算符，其中值可以为 null .当操作员不使用 null 时，我在寻找文档，但没有找到.在这两种情况下，它都没有返回文档(即使$ ne，$ gte和$ lte确实返回了文档；这意味着有些文档等于或不等于 null ).

In MongoDB I would like to use $gt and $lt comparision operators where the value could be null. When the operators did not work with null, I looked for documentation but found none. In both cases it returned no documents (even though $ne, $gte, and $lte did return documents; meaning there were documents that were both equal to and not equal to null).

我希望$ gt基本上像$ ne一样运行(因为 null 类型

I would expect $gt to essentially operate like $ne (as the null type Mongo comarison order is so low) and $lt to return nothing for the same reason.

我希望这会可行，因为我传递给查询的值是变量(可能为 null )，并且我不想为 null .

I was hoping this would work as the value I pass to the query is variable (potentially null), and I don't want to have to write a special case for null.

鉴于以下集合，我正在研究的示例:

Example of what I was expeccting, given the following collection:

{
  id: 1,
  colNum: null
}
{
  id: 2,
  colNum: 72
}
{
  id: 3
}

我期望以下查询:

db.testtable.find( { "colNum" { $gt : null } } )

要返回:

{
  id: 2,
  colNum: 72
}

但是，什么也没有返回.

However, nothing was returned.

是否存在$ gt和$ lt不能与 null 一起使用的原因，还是MongoDB错误，还是实际上应该起作用并且可能存在用户错误? ?

Is there a reason that $gt and $lt don't seem to work with null, or is it a MongoDB bug, or is it actually supposed to work and there is likely a user error?

推荐答案

细腻细节

通读最新的Mongo源代码，进行涉及null的比较基本上有2种情况:

Nitty-Gritty Details

Reading through the latest Mongo source, there's basically 2 cases when doing comparisons involving null:

1. 如果规范类型被比较的BSON元素的个数是不同的，只有相等比较(==，>=，<=)为空&. undefined将返回true;否则，任何与null的比较都将返回false.
   注意:没有其他BSON类型具有与null相同的规范类型.
2. 如果规范类型相同(即，两个元素都是null)，然后规范类型，然后与0进行请求的比较.
   例如，null > null将转换为(5-5) > 0 --> False，因为null的规范类型为5.
   同样，null < null会翻译为(5-5) < 0 --> False.

1. If the canonical types of the BSON elements being compared are different, only equality comparisons (==, >=, <=) of null & undefined will return true; otherwise any comparison with null will return false.
   Note: No other BSON type has the same canonical type as null.
2. If the canonical types are the same (i.e., both elements are null), then compareElementValues is called. For null, this just returns the difference between the canonical type of both BSON elements and then carries out the requested comparison against 0.
   For example, null > null would translate into (5-5) > 0 --> False because the canonical type of null is 5.
   Similarly, null < null would translate into (5-5) < 0 --> False.

这意味着null只能等于null或undefined.其他任何涉及null的比较都将始终返回false.

This means null can only ever be equal to null or undefined. Any other comparison involving null will always return false.

更新的答案:

比较运算符的文档( $ gt ， $ lt )引用

The documentation for the comparison operators ($gt, $lt) references the documentation which you originally linked, which implies that the comparison operators should work with null. Furthermore, query sorting (i.e., db.find().sort()) does accurately follow the documented Comparison/Sort behavior.

这至少是不一致的.我认为值得向 MongoDB的JIRA网站提交错误报告.

This is, at the very least, inconsistent. I think it would be worth submitting a bug report to MongoDB's JIRA site.

原始答案:

我不认为这种行为是错误的.

I don't think this behavior is a bug.

针对Javascript的普遍共识是，undefined表示未分配，而null表示已分配，但未定义.除了相等性之外，将值与未定义的值进行比较是没有意义的，至少在数学意义上是这样.

The general consensus for Javascript is that undefined means unassigned while null means assigned but otherwise undefined. Value comparisons against undefined, aside from equality, don't make sense, at least in a mathematical sense.

鉴于BSON大量使用JavaScript，这也适用于MongoDB.

Given that BSON draws heavily from JavaScript, this applies to MongoDB too.

这篇关于具有null的MongoDB比较运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！