MongoDB: C# Mongo Driver Query for records where the field is null or not set

Today in the article we shall learn how to write C#.NET-MongoDB Find field is null or not set filter search using C# MongoDB driver.

Today in this article, we will cover below aspects,

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

If interested to know how to run queries using CLI and UI Compass, we will cover that also.

Getting started

I have a sample MongoDB document that has below schema,

{

 "_id":"5db5a4476997188b2722c820"}
 "Name":"Design Patterns",
 "Price":54.93,
 "Category":"Computers",
 "Author":"Ralph Johnson"

}

Let’s now search all the documents where the Author field is NULL. This field is Null is and does not exist in some documents.

Field is null or not set – Equality Filter in MongoDB

Below equality query matches documents that either contain the Author field whose value is NULL or that do not contain the Author field.

var tempQuery = new BsonDocument
                 {
                     {"Author" , new BsonDocument {
                         { "$eq", BsonNull.Value }
                     }}
                 };

OR

Alternatively, you can use below other query using Builders patterns,

var filter1 = Builders.Filter.Eq("Author", BsonNull.Value);

Above both queries are the same and produce the expected result.

The below query produces 2 results,

• matches “Author” value is NULL

• and matches “Author” fields don’t exist

The above query is simple to develop using BsonDocument. You can create more complex queries by combining multiple criteria as required.

Also using the Filter query above discussed below we get the same results.

Field is not set or does not exist in MongoDB

If you would like to query for fields that do not exist in MongoDB at all then use the below query alone,

var tempQuery = new BsonDocument
                 {
                     {"Author" , new BsonDocument {
                         { "$exists" , false}
                     }}
                 };

Note:

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

In the above query now 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 or event not set

Field is null in MongoDB

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

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

In such cases please use the below query,

Multiple filters via MongoDB C# driver

If you need to combine both null or fields not set both queries, please use the below query to achieve the same.

Below we are combining two filter conditions,

var tempQuery = new BsonDocument
                 {
                     {"Author" , new BsonDocument {
                         { "$exists" , false},
                         { "$eq", BsonNull.Value }
                     }}
                 };

OR

Below we are combining two or multiple filters conditions together using C# Builder as below,

var filter1 = Builders.Filter.Eq("Author", BsonNull.Value);
var filter2 = Builders.Filter.Exists("Author", false);

var matchedDocument = collection1.FindSync(filter1 & filter2).ToList();

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 for Field is Empty

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.