Querying Null or Missing Values in MongoDB – Ultimate Guide

Querying Null Or Missing Values In Mongodb Featured Image

In this tutorial, I will discuss querying null or missing values in MongoDB and how you can do it.

This tutorial will explain how querying null or missing values can be done using the simplest of ways. I will provide different examples so you can look up null or missing values inside your database. we will be doing this in the mongo shell.

So, let us start with the guide on querying null or missing values in MongoDB.

Querying Null or Missing Values in MongoDB in the Mongo Shell

Let us start with the different examples for our tutorial on querying null or missing values in MongoDB.

Step1: Insert a Document with a Null Value

  • Run up the MongoDB server
  • In the Mongo shell, select the database you want to use and navigate into it like this:
show dbs
use farmStand
  • Insert a document with a null value to help with querying null or missing values in MongoDB:
> db.products.insertOne({
...         "name" : "Uncle Ivon’s Freshly-baked Black Currant Pie",
...         "price" : 7.66,
...         "category" : null,
... })

{
        "acknowledged" : true,
        "insertedId" : ObjectId("616f2027041f3432a7abda05")
}
  • Let us print all documents in our collection to check if it has been added:
> db.products.find().pretty()

{
        "_id" : ObjectId("6054f4ba0e761c066439114b"),
        "name" : "Fairy Eggplant",
        "price" : 1,
        "category" : "vegetable",
        "__v" : 0
}
{
        "_id" : ObjectId("6054f4ba0e761c066439114c"),
        "name" : "Organic Goddess Melon",
        "price" : 4.99,
        "category" : "fruit",
        "__v" : 0
}
{
        "_id" : ObjectId("6054f4ba0e761c066439114d"),
        "name" : "Organic Mini Seedless Watermelon",
        "price" : 3.99,
        "category" : "fruit",
        "__v" : 0
}
{
        "_id" : ObjectId("6054f4ba0e761c066439114e"),
        "name" : "Organic Celery",
        "price" : 1.5,
        "category" : "vegetable",
        "__v" : 0
}
{
        "_id" : ObjectId("6054f4ba0e761c066439114f"),
        "name" : "Chocolate Whole Milk",
        "price" : 2.69,
        "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.

Step 2: Query Null in MongoDB Using Exact Match

In this example, we will perform an exact match to query for null values:

> db.products.find( { category : null } ).pretty()

{
        "_id" : ObjectId("616f2027041f3432a7abda05"),
        "name" : "Uncle Ivon’s Freshly-baked Black Currant Pie",
        "price" : 7.66,
        "category" : null
}

With the products.find( {category:null} ) query, we can pull all the fields that have a null value category.

Step 3: Querying Null Values in MongoDB Using Type Check

In this example, I will use type checking for querying null or missing values in MongoDB.

  • Let us look for a document with the category field as null. To do this, we will use a $type operator and set its value as 10. In BSON data types, the Null value is denoted as 10. Hence, we will set the number 10 as the value.
> db.products.find( { category : { $type: 10 } } ).pretty()

{
        "_id" : ObjectId("616f2027041f3432a7abda05"),
        "name" : "Uncle Ivon’s Freshly-baked Black Currant Pie",
        "price" : 7.66,
        "category" : null
}

Step 4: Inserting a Document with Missing Values

Let us now insert a document that has a few values missing on it. this will help us in querying null or missing values in MongoDB.

> db.products.insertOne( { "name" : "Fresh Pineapple Pulp Juice" } )

{
        "acknowledged" : true,
        "insertedId" : ObjectId("616f234f041f3432a7abda06")
}
  • Let us check if the document was added:
> db.products.find().pretty()

{
        "_id" : ObjectId("6054f4ba0e761c066439114b"),
        "name" : "Fairy Eggplant",
        "price" : 1,
        "category" : "vegetable",
        "__v" : 0
}
{
        "_id" : ObjectId("6054f4ba0e761c066439114c"),
        "name" : "Organic Goddess Melon",
        "price" : 4.99,
        "category" : "fruit",
        "__v" : 0
}
{
        "_id" : ObjectId("6054f4ba0e761c066439114d"),
        "name" : "Organic Mini Seedless Watermelon",
        "price" : 3.99,
        "category" : "fruit",
        "__v" : 0
}
{
        "_id" : ObjectId("6054f4ba0e761c066439114e"),
        "name" : "Organic Celery",
        "price" : 1.5,
        "category" : "vegetable",
        "__v" : 0
}
{
        "_id" : ObjectId("6054f4ba0e761c066439114f"),
        "name" : "Chocolate Whole Milk",
        "price" : 2.69,
        "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"
}

Step 5: Querying Missing Value in MongoDB Using Existence Check

In this example, I will query for missing values by checking if a field exists using the $exists operator.

See more examples of the $exists operator here.

We will now look for documents that do not contain the price field by setting the value of the $exists operator as false for the price field.

> db.products.find( { price : { $exists: false } } ).pretty()
{
        "_id" : ObjectId("616f234f041f3432a7abda06"),
        "name" : "Fresh Pineapple Pulp Juice"
}

Perfect! We now have successfully learned querying null or missing values in MongoDB.

Conclusion

In this article, we learned about querying null or missing values in MongoDB with different examples.

Noteworthy References

MongoDB Docs