The term “is not null” means that there is some value present, except for null. It can be anything, like an empty string, zero, or any other value but not null.
The null values are useless in terms of any data they provide. Even if you try to perform any type of statistical operation, these values can disturb the results.
So it is better to exclude null before doing any kind of operation and that means we have to consider only the “not null” values. Let’s find out how.
Query For Is Not Null in MongoDB
If we talk about MongoDB, querying “is not null” means that a field in a document exists and can be anything but not “null”. To query for “is not null” documents we can use the $ne operator.
The $ne operator or not equal operator helps us to query for values that are not equal to the passed argument value. In other words, it matches only documents where a particular field has a value that differs from the passed value.
So if we pass “null” as an argument in $ne, the returns documents will not contain a null value for the specified field. Yes, that is how we can query for “is not null” in MongoDB.
Let’s look at some examples of how we can achieve this.
Example 1: Query for Is Not Null Value on String Field
Let’s use the find method to get all the documents that we will use for our example:
> 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
}
In this example, we will pass null to the $ne operator to filter documents by negating null values for a specific 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.
Example 2: Query for Is Not Null Value on Numeric Field
In this example, we will be picking those documents that do not have a numeric-value field score value as 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.
Example 3: Query for Non-Empty Value
In this example, we will use the $ne operator to query for documents where the value of a field is not an empty string 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 good to go.
Let us now use the $ne operator to query for non-empty values:
> 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.
Example 4: Query for 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 look 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.
Summary
In short, the $ne operator can be used to get the documents containing a specified field that is not null by passing “null” as an argument with it. Querying for “is not null” helps when someone wants to retrieve documents from the database but avoids those where some specified field values are null. The examples shown above can be very helpful if you look carefully at how we tried to avoid documents having null values for the desired fields.
Also Read: Check If a Field Exists in MongoDB
Reference
https://stackoverflow.com/questions/4057196/how-do-you-query-for-is-not-null-in-mongo
