Menu
I was checking logic for querying on non-values and noticed when using the mongo shell, it differentiates between undefined and null values.
> use test
> db.test.insert({ a : 1, b : null, c : undefined })
> db.test.insert({ a : null, b : undefined, c : 1 })
> db.test.insert({ a : undefined, b : 1, c : null })
When you query on the collection, you get this:
> db.test.find()
{ "_id" : ObjectId("52d95575c9333565e80ccb22"), "a" : 1, "b" : null, "c" : null }
{ "_id" : ObjectId("52d9557fc9333565e80ccb23"), "a" : null, "b" : null, "c" : 1 }
{ "_id" : ObjectId("52d95586c9333565e80ccb24"), "a" : null, "b" : 1, "c" : null }
When you query on null however, it only retrieves records who was explicitly set to null.
> db.test.find({ a : null })
{ "_id" : ObjectId("52d9557fc9333565e80ccb23"), "a" : null, "b" : null, "c" : 1 }
Is this a bug in MongoDB? How can I properly query for null/ undefined/ non-set fields?
So I can query for not-set values with this:
db.test.find({ $or : [ { a : null }, { a : { $exists : false } } ] })
But in this example though, it still only returns the single record:
{ "_id" : ObjectId("52d9557fc9333565e80ccb23"), "a" : null, "b" : null, "c" : 1 }
Anyone know why/ how MongoDB differentiates between undefined and null? Is this an issue with how the data was entered in MongoDB?
440
➤ Use the Select your language drop-down menu in the upper-right to set the language of the following examples. Different query operators in MongoDB treat null values differently. The query returns both documents in the collection. The query returns only the document where the item field has a value of null.
The value null must be explicitly set for a property. For example, here is an item that has the creationDate property set to null: A property with a null value must have it explicitly assigned. Properties not defined in an item have an undefined value.
In aggregate system functions, undefined values will simply be omitted from the calculation and will not impact the result. For example, a query like SELECT SUM (c.total) FROM c has an implicit IS_DEFINED (c.total) filter. Learn more about Azure Cosmos DB query operators.
In the example item above, the property isRegistered has a value of undefined because it is omitted from the item. Azure Cosmos DB supports two helpful type checking system functions for null and undefined properties, both of which use the index:
If you want to return a document where a field exists AND is not null, use { a : {$ne : null}}
Undefined and null values are different, but the shell shows them both as null - https://jira.mongodb.org/browse/SERVER-6102
156
In case you wonder, why MongoDB casts undefined to null instead of just ignoring such properties - ignoreUndefined flag could solve this behaviour.
https://mongodb.github.io/node-mongodb-native/2.1/reference/connecting/connection-settings/
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
