How to Query For Is Not Null in MongoDB?

Query For Is Not Null Featured Image

In this guide, I will explain how to query non-null values in MongoDB.

To query for is not null value, we can use the $ne operator as well as the $eq operator and specify the desired value that we want to query for.

This guide aims to provide readers with different ways and examples for the same to query for is not null values in MongoDB. So, let us get this guide started.

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.

Below is the collection I will be using to illustrate some operations:

> 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, I will be passing an expression to the $ne operator that negates a null value for a specific field to eventually query for 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 have the rating field’s value as null.

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

In this example, I will be picking those documents that do not have numeric-value field value 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, I 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, I will add a few to the collection.

> 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 is 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, we did not find the newly added documents this time.

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

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

Let us look for values 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

Learn how to query for is not null value in MongoDB with this guide.

References

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