How to Query For Is Not Null in MongoDB in a Specific Field?

In a database, it is often necessary to filter out data that is irrelevant to the query. The $ne operator helps us do precisely that, it allows us to query for values that are not equal to a specified value. In other words, it will return all the records whose value differs from the specified value.

We can specify null here so that the documents we get will not contain documents with a null value.

This tutorial will guide you with different techniques and examples for querying for is not null values in MongoDB. 

How to Query for is Not Null Value in MongoDB

Let us get started by taking a look at the examples of how we can achieve this.

For demonstration, we already have inserted many documents in a collection name “movies” where we will perform operations for querying for is not null values with different techniques.

The following is a collection that we will use to illustrate the operation:

> db.movies.find().pretty()
{
        "_id" : ObjectId("60322d3501cd70079c48cb65"),
        "title" : "Enchanted",
        "year" : 2006,
        "score" : 10,
        "rating" : "PG",
        "__v" : 0
}
{
        "_id" : ObjectId("60322d3501cd70079c48cb67"),
        "title" : "Final Destination II",
        "year" : 2015,
        "score" : 10,
        "rating" : "PG-13",
        "__v" : 0
}
{
        "_id" : ObjectId("6190189ef5c8903629012fe1"),
        "title" : "Fifty Shades of Grey",
        "year" : 2015,
        "score" : 10,
        "rating" : "NC-17",
        "__v" : 0
}
{
        "_id" : ObjectId("6190189ef5c8903629012fe2"),
        "title" : "Cars",
        "year" : 2006,
        "score" : 8,
        "rating" : null,
        "__v" : 0
}
{
        "_id" : ObjectId("6190189ef5c8903629012fe3"),
        "title" : "The Matrix",
        "year" : 1999,
        "score" : null,
        "rating" : "R",
        "__v" : 0
}

Using the $ne Operator to Query for is Not Null Value on a String Field

In this example, we will pass an expression to the $ne operator that negates a null value for a specific field to query for eventually is not null value on a string field.

> db.movies.find({ rating : {$ne : null} }).pretty()
{
        "_id" : ObjectId("60322d3501cd70079c48cb65"),
        "title" : "Enchanted",
        "year" : 2006,
        "score" : 10,
        "rating" : "PG",
        "__v" : 0
}
{
        "_id" : ObjectId("60322d3501cd70079c48cb67"),
        "title" : "Final Destination II",
        "year" : 2015,
        "score" : 10,
        "rating" : "PG-13",
        "__v" : 0
}
{
        "_id" : ObjectId("6190189ef5c8903629012fe1"),
        "title" : "Fifty Shades of Grey",
        "year" : 2015,
        "score" : 10,
        "rating" : "NC-17",
        "__v" : 0
}
{
        "_id" : ObjectId("6190189ef5c8903629012fe3"),
        "title" : "The Matrix",
        "year" : 1999,
        "score" : null,
        "rating" : "R",
        "__v" : 0
}

Great! We found 4 out of 5 such documents that do not contain the rating field value as null.

Using the $ne Operator to Query for is Not Null on a Numeric Value Field

In this example, we will be picking those documents that do not have a numeric-value field value that is null.

> db.movies.find({ score : {$ne : null} }).pretty()
{
        "_id" : ObjectId("60322d3501cd70079c48cb65"),
        "title" : "Enchanted",
        "year" : 2006,
        "score" : 10,
        "rating" : "PG",
        "__v" : 0
}
{
        "_id" : ObjectId("60322d3501cd70079c48cb67"),
        "title" : "Final Destination II",
        "year" : 2015,
        "score" : 10,
        "rating" : "PG-13",
        "__v" : 0
}
{
        "_id" : ObjectId("6190189ef5c8903629012fe1"),
        "title" : "Fifty Shades of Grey",
        "year" : 2015,
        "score" : 10,
        "rating" : "NC-17",
        "__v" : 0
}
{
        "_id" : ObjectId("6190189ef5c8903629012fe2"),
        "title" : "Cars",
        "year" : 2006,
        "score" : 8,
        "rating" : null,
        "__v" : 0
}

Amazing! We again found 4 out of 5 documents that did not have null as the value on the score field which holds a numeric value.

Using the $ne Operator to Query Non-Empty Values

In this example, we will simply advise the $ne operator to query for is not null value in MongoDB by specifying a blank string.

Since we do not have any such document with a blank string value, let’s first add a few to the collection using the insertMany() method.

> db.movies.insertMany([
... {
...         "title" : "Salt",
...         "year" : 2010,
...         "score" : 9,
...         "rating" : "",
...         "__v" : 0
... },
... {
...         "title" : "Knowing",
...         "year" : 2009,
...         "score" : 8,
...         "rating" : "",
...         "__v" : 0
... }
... ])

{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("61901f82f5c8903629012fe4"),
                ObjectId("61901f82f5c8903629012fe5")
        ]
}

Okay, now that we have 2 more documents added to our collection, we are now good to go. Let us now use the $ne operator to query for not null value by specifying a blank string.

> db.movies.find({ rating : {$ne : ""} }).pretty()
{
        "_id" : ObjectId("60322d3501cd70079c48cb65"),
        "title" : "Enchanted",
        "year" : 2006,
        "score" : 10,
        "rating" : "PG",
        "__v" : 0
}
{
        "_id" : ObjectId("60322d3501cd70079c48cb67"),
        "title" : "Final Destination II",
        "year" : 2015,
        "score" : 10,
        "rating" : "PG-13",
        "__v" : 0
}
{
        "_id" : ObjectId("6190189ef5c8903629012fe1"),
        "title" : "Fifty Shades of Grey",
        "year" : 2015,
        "score" : 10,
        "rating" : "NC-17",
        "__v" : 0
}
{
        "_id" : ObjectId("6190189ef5c8903629012fe2"),
        "title" : "Cars",
        "year" : 2006,
        "score" : 8,
        "rating" : null,
        "__v" : 0
}
{
        "_id" : ObjectId("6190189ef5c8903629012fe3"),
        "title" : "The Matrix",
        "year" : 1999,
        "score" : null,
        "rating" : "R",
        "__v" : 0
}

Obviously, in the result value, we don’t get the newly added document this time.

Using the $eq Operator to Specify a Non-Null Value

In this example, we will use the $eq operator to query for a non-null value. In layman’s terms, we will actually be passing a value we want to lookup for to query for that is not a null value.

Let us look for movies that were released in 2015.

> db.movies.find({ year: {$eq : 2015}}).pretty()
{
        "_id" : ObjectId("60322d3501cd70079c48cb67"),
        "title" : "Final Destination II",
        "year" : 2015,
        "score" : 10,
        "rating" : "PG-13",
        "__v" : 0
}
{
        "_id" : ObjectId("6190189ef5c8903629012fe1"),
        "title" : "Fifty Shades of Grey",
        "year" : 2015,
        "score" : 10,
        "rating" : "NC-17",
        "__v" : 0
}

Impressive! We found 2 movies in our collection that were released in 2015.

Conclusion

We can use the $ne operator to get the documents containing the field which is not null. You may need this operator very often when you are fetching documents from a database and want to avoid those where some specified field value is null. We hope this tutorial provides you with all the answers to your question. 

References

https://stackoverflow.com/questions/4057196/how-do-you-query-for-is-not-null-in-mongo

Aneesha S
Aneesha S
Articles: 172