MongoDB provides several simple ways to query for null or missing values. This can be useful in many situations such as when you want to clear data that does not have a required field or you want to set a default value for data that has a null value or maybe you want your User must enter a value where it is null. For all these, you can easily find null value fields in MongoDB.
In this tutorial, we will learn how to do it using several methods so you can choose one according to your need. Let’s start by inserting a null value.
Insert a Document with a Null Value using Mongo Shell
To start off, let’s use the insertOne() method to insert null value.
Follow the measures below to get started:
- Start the MongoDB server
- Pass the below commands to list all the available databases and move into one of them, for example
show dbs
use farmStand
- Use the collection name with the method insertOne() to insert the document.
> db.products.insertOne({
... "name" : "Uncle Ivon’s Freshly-baked Black Currant Pie",
... "price" : 7.66,
... "category" : null,
... })
{
"acknowledged" : true,
"insertedId" : ObjectId("616f2027041f3432a7abda05")
}
Here you can see we have inserted a document with a name field and a price field and passed null as the value of the category field.
- Now, let us pretty print all the documents of the collection to check if it has been added.
> db.products.find().pretty()
{
"_id" : ObjectId("6054f4ba0e761c066439114b"),
"name" : "Fairy Eggplant",
"price" : 2,
"category" : "vegetable",
"__v" : 0
}
{
"_id" : ObjectId("6054f4ba0e761c066439114c"),
"name" : "Organic Goddess Melon",
"price" : 5,
"category" : "fruit",
"__v" : 0
}
{
"_id" : ObjectId("6054f4ba0e761c066439114d"),
"name" : "Organic Mini Seedless Watermelon",
"price" : 4,
"category" : "fruit",
"__v" : 0
}
{
"_id" : ObjectId("6054f4ba0e761c066439114e"),
"name" : "Organic Celery",
"price" : 3,
"category" : "vegetable",
"__v" : 0
}
{
"_id" : ObjectId("6054f4ba0e761c066439114f"),
"name" : "Chocolate Whole Milk",
"price" : 1,
"category" : "dairy",
"__v" : 0
}
{
"_id" : ObjectId("616f2027041f3432a7abda05"),
"name" : "Uncle Ivon’s Freshly-baked Black Currant Pie",
"price" : 7.66,
"category" : null
}
Perfect! We now have our null field value document added to our collection. This way when we don’t have value to pass to a specific field we can set it as null.
Query Null Value in MongoDB Using Exact Match
We can directly query the null using exact match, there is no additional operator required, we just have to use the find() method and pass the type query having the name of the field with the value as null.
Syntax:
db.collectionName.find({ fieldName: null })
where fieldName is the name of the field for which you want to check null, and collectionName is the name of the collection containing the document with that field.
Example:
Below is an example where we will perform an exact match to query for finding null in the category field in the products collections:
> db.products.find( { category : null } ).pretty()
{
"_id" : ObjectId("616f2027041f3432a7abda05"),
"name" : "Uncle Ivon’s Freshly-baked Black Currant Pie",
"price" : 7.66,
"category" : null
}
See we got the document having the category null.
Query Null Value in MongoDB Using Type Check
We can also filter documents having a null value field using a type check. For this, we need to use the $type operator which can take a numeric value of 10 representing the null BSON type.
Syntax:
db.collectionName.find({ fieldName: { $type: 10 } })
where fieldName is the name of the field you want to check for null.
Example:
Below is an example where we will use the $type operator to query for null in the category field:
> db.products.find( { category : { $type: 10 } } ).pretty()
{
"_id" : ObjectId("616f2027041f3432a7abda05"),
"name" : "Uncle Ivon’s Freshly-baked Black Currant Pie",
"price" : 7.66,
"category" : null
}
See we got the exact same document as in the previous example, having the category null.
Insert a Document with Missing Value
Let us now insert a document that has some values missing.
> db.products.insertOne( { "name" : "Fresh Pineapple Pulp Juice" } )
{
"acknowledged" : true,
"insertedId" : ObjectId("616f234f041f3432a7abda06")
}
Let’s see if the document has been added:
> db.products.find().pretty()
{
"_id" : ObjectId("6054f4ba0e761c066439114b"),
"name" : "Fairy Eggplant",
"price" : 2,
"category" : "vegetable",
"__v" : 0
}
{
"_id" : ObjectId("6054f4ba0e761c066439114c"),
"name" : "Organic Goddess Melon",
"price" : 5,
"category" : "fruit",
"__v" : 0
}
{
"_id" : ObjectId("6054f4ba0e761c066439114d"),
"name" : "Organic Mini Seedless Watermelon",
"price" : 4,
"category" : "fruit",
"__v" : 0
}
{
"_id" : ObjectId("6054f4ba0e761c066439114e"),
"name" : "Organic Celery",
"price" : 3,
"category" : "vegetable",
"__v" : 0
}
{
"_id" : ObjectId("6054f4ba0e761c066439114f"),
"name" : "Chocolate Whole Milk",
"price" : 1,
"category" : "dairy",
"__v" : 0
}
{
"_id" : ObjectId("616f2027041f3432a7abda05"),
"name" : "Uncle Ivon’s Freshly-baked Black Currant Pie",
"price" : 7.66,
"category" : null
}
{
"_id" : ObjectId("616f234f041f3432a7abda06"),
"name" : "Fresh Pineapple Pulp Juice"
}
Query Missing Fields in MongoDB Using Existence Check
To query the document having missing values we can use the existence check by applying the $exists operator.
Syntax:
db.collectionName.find({ fieldName: { $exists: false } })
where fieldName is the name of the field you want to check for null.
Example:
Below is an example where we will query documents that do not have a price field by setting the value of the $exists operator to false for the price field.
> db.products.find( { price : { $exists: false } } ).pretty()
{
"_id" : ObjectId("616f234f041f3432a7abda06"),
"name" : "Fresh Pineapple Pulp Juice"
}
See we got a document with a missing value. In this way, we have also learned how to query missing values in MongoDB.
Read More: Update Multiple Documents using updateMany() in MongoDB
Conclusion
In this tutorial, we have learned the process of inserting null and missing value fields in MongoDB and then querying them using Mongo shell. The methods explained are simple yet powerful. We recommend you demonstrate these concepts on your own to reinforce your understanding.
If you’re new to MongoDB or Mongoose, we recommend reading this tutorial to enhance your knowledge of the MongoDB database. Hope this tuitorial helps you to understand querying null or missing field values in MongoDB.
