MongoDB Query for records where the field is null or not set

Today in the article we shall learn how to MongoDB Query for field is null or not set i.e doesn’t exist in the database.

We shall verify queries using MongoShell or UI tools like a Compass UI.

Today in this article, we will cover below aspects,

We shall also be running the query using the .NET C# Mongo driver.

If interested to know how to run queries using .NET C# mongo driver please check this article,

Getting started

I have a sample MongoDB document as below in one of the collections. Here we shall be trying to search all the documents where the Author field is NULL or not set in documents.

The field is not set

The below query matches documents that do not contain the Author field.

So far above Mongo documents, we shall get results for only “_id”:”5db5a4476997188b2722c820“

Query Pattern

{  Field Name:  {$exists:false}  }

Example Query

{ Author: {$exists:false} }

Results:

Using Mongo shell

Using Compass UI

Note:

If $exists is false, the query returns only the documents that do not contain the field.

In the above query, we shall get the result as only 1 document.

 $exists when set as true, it matches the documents that contain the field, including documents where the field value is null.

The field is null

The below query matches documents that contain the Author field which is set as null.

Here we are assuming the fields already exist and don’t want to consider the records where records where fields do not exist.

So far above Mongo documents, we shall get results for only id = 5ff50353a29ce9564c2724e2

Query Pattern

{$and:[{Field Name:{$type:'null'}}]}

Example Query

{$and:[{Author:{$type:'null'}}]}

Results:

Using Mongo shell

Using Compass

The field is null or not set

Let’s now see if you have requirements to find the records where Field is not set or does not exist in MongoDB.

Query Pattern

{$and:[{Field Name:null }]}

Query Example

{$and:[{Author:null }]}

The above query does work for the below 2 conditions,

• matches “Author” value is NULL
• and matches “Author” fields don’t exist

Results:

Mongo Shell

Compass UI

That’s all, So today we learned the simple Mongo query for identifying the records where the field is null or not set. It’s pretty easy to prepare the query for such needs.

References:

• MongoDB Naming Conventions and Standards

• MongoDB Query – Fields not set or Field is null

Do you have any comments or ideas or any better suggestions to share?

Please sound off your comments below.

Happy Coding !!

---

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.

---