MongoDB comparison operators with null

Question

In MongoDB, I would like to use the comparison operators $ gt and $ lt, where the value may be null. When the operators did not work with a null value, I searched the documentation but could not find it. In both cases, he did not return any documents (although $ ne, $ gte and $ lte did indeed return documents, which means that there were documents equal and not equal to zero).

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

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

An example of what I studied, given the following collection:

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

I expect the following request:

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

To return:

 { id: 2, colNum: 72 } 

However, nothing was returned.

Is there a reason that $ gt and $ lt do not seem to work with zero, or is this a MongoDB error, or should it actually work, and is there probably a user error?

Answer 1

File Details

Reading through the last Mongolian source , basically 2 cases when compared to null :

• If the canonical types of the compared BSON elements are different from each other, only comparison comparisons ( == , >= , <= ) null and undefined will return true ; otherwise, any comparison with null will return false .
  Note. No other type of BSON has the same canonical type as null .
• If the canonical types are the same (i.e. both elements are null ), then compareElementValues. For null this simply returns the difference between the canonical type of both BSON elements and then performs the requested comparison with 0 .
  For example, null > null translates to (5-5) > 0 --> False , because the canonical type null is 5.
  Similarly, null < null translates to (5-5) < 0 --> False .

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

Is this a mistake?

Updated answer:

The documentation for comparison operators ( $ gt , $ lt ) refers to the documentation that you originally linked , which implies that comparison operators must work with null . In addition, query sorting (i.e., db.find().sort() ) does exactly follow the documented Comparison / Sort behavior.

This is at least inconsistent. I think it would be advisable to send a bug report to the MongoDB JIRA website .

---

Original answer:

I do not think this behavior is a mistake.

The general consensus for Javascript is that undefined means non-assignment, and null means assigned, but otherwise undefined. Comparing values ​​with undefined, in addition to equality, does not make sense, at least in the mathematical sense.

Given that BSON relies heavily on JavaScript, this also applies to MongoDB.