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:
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.