MongoDB Bytes #6 Querying when one field is null or does not exist

G’day folks! :wave:

Today, we are going to quickly explore how to query for documents when one field has a value null and how MongoDB behaves when one field doesn’t exist.

Consider the following sample collection:

{ _id: 1, val: null },
{ _id: 2, val: 1 },
{ _id: 3, val: 2 }

As we can ssee, document with _id:1 has a val field that is equal to null. When we query for the document that is null, everything works in the expected way:

> db.sample.find({val:null})
{ _id: 1, val: null }

So far, so good! However, one thing to note is that null also means does not exist, so if we query something like this:

db.sample.find({ vals:null })

This returns all documents that do not have this key.

> db.sample.find({vals:null})

{ _id: 1, val: null },

{ _id: 2, val: 1 },

{ _id: 3, val: 2 }

One solution to get only the keys whose value is null is by using $exists:

db.sample.find({vals: {"$eq":null,"$exists":true}})

This returns no document since vals does not exist in our collection.

One thing to note about $exists is that although the method works, using $exists typically involves lower performance compared to an equality match. In the worst cases, it scans the whole collection, and using indexes is highly recommended when using $exists.
For more details, do read the documentation on how to improve performance of $exists using indexes.

To conclude,

  1. Null behaves as expected when the key exists in our documents.
  2. Null also means ‘does not exist’ and so, if we query for a field that does not exist in our document and query for its null value, MongoDB will return all the documents in that collection.
  3. It is advised to use $exists to match the documents that contain the field, including documents where the field value is null.
  4. $exists typically can also result in lower performance of query and using indexes is highly recommended.

For more detailed information, do check out our documentation: Query for Null or Missing Fields. :smile:

2 Likes