Mongodb Null or Empty - A Look at Some Edge Cases for MongoDB and How to Query for Them

More often than not every document in a database collection will not have all the same fields as other documents or it might be null or empty. So database administrators need to understand how to take this into account in their queries. In this article we’ll demonstrate a few queries that can help you filter out documents that either don’t have a field, it’s null, or it’s empty. We’ll also show you the opposite where you query for the documents that don’t have a field, have it set to null, or it’s empty. We’ll show you several different techniques to do it so you can choose the one that fits your situation.

How We’ll Demo

We’ll be demonstrating how to do this in the MongoDB Shell, but the same concepts will apply to your drivers and the syntax is very similar from the Shell to the drivers.

Helpful operators

Let’s start with one of the most straightforward ways of approaching the problem. Let’s say we wanted to get all the products where the department field was either null or completely didn’t exist.

Equality Filter

We could use the find() command to check for equality to null. The filter for { department : null } would match documents with the department field explicitly set to null OR documents where the that do not contain the department field. This is a nuance that you’ll want to commit to memory. So the command:

would return the following:

BSON Data Type Check

You could also filter on what type of BSON data is stored in a field. The types are enumerated, meaning each type Integer, Boolean, Double etc each have a number corresponding to that type. Here’s a condensed version of that list:

So using the find() method we could filter only for documents that had a Null type:

which returns:

Note This time the documents that didn’t have a department field were not returned.

Existence Check

So can we check for only documents where a field DOES NOT exist? Yes. You can do so using the $exists operator. Let’s check for documents in our product collection where the department field does not exist. That query would look like this:

This query returns:

Equals and Not Equals Operators $eq $ne

We can also use the equals and not equals operators $eq and $ne to check for null and fields that don’t exist. Let’s see a couple examples:

This returns:

Note that checking for equality to null here returns documents where the department field doesn’t exist as well. This is one advantage the existence and type check have over the other queries in that they are very explicity about what they return.

The opposite query is quite helpful and comes up in many real world situations where you only want to operate on documents where that field exists with a value:

This query returns:

If we wanted to find documents where the department field did not existwe would filter for the Undefined type (6):

The ‘Not In’ operator $nin

The last operator we’ll show you is the ‘Not In’ operator or $nin. You provide this operator with a field and an array of values and it returns documents where the specified field is not in the list. It also returns documents where that field does not exist.

Let’s look at an example:

This query returns:

As you can see this is also a great query to find documents where a field does not exist or is not defined.

Conclusion

We hope this article has provided you with some insight on how to create or modify queries so that you’re taking into account outlier documents that have a null field or that field is not defined. There can be a lot of nuance to these queries but they are invaluable in the real world. If you have any questions please reach out to us at Object Rocket. We’re great at managing databases so please contact us if you need your data in hands you can trust.