In a regular SQL database, you can check if a column is null by using IS NULL

For example if you wanted to return all rows where the age is null, you would do the following

SELECT * 
FROM SomeTable
WHERE age IS NULL

In a NoSQL database it is possible that half the documents in a collection are omitted and maybe five are there with the value null. How can you know if the field is missing or has the value null?

Let’s take a quick look. First insert the following document into your collection

db.Blog.insert( { name : "Denis2" } )

As you can see it just has a name. Now let’s add another document this time with age as well, we will make the age NULL

db.Blog.insert( { name : "Denis",  age : NULL} )

You get the following error

Sun Feb 17 13:44:58 ReferenceError: NULL is not defined (shell):1

This error occurs because you need to pass null in lowercase

db.Blog.insert( { name : "Denis",  age : null } )

Now if you execute the following

db.Blog.find({age:null});

You get back both document

{ “_id” : ObjectId(“512118a7c1eca3d7ffcd00f9”), “name” : “Denis”, “age” : null }

{ “_id” : ObjectId(“512123d9c1eca3d7ffcd00fa”), “name” : “Denis2” }

In order to return the document where the value that is stored is null, you can use $type: 10. What that means is that the field is of BSON Type Null

db.Blog.find( { age: { $type: 10 } } )

Here is the output

{ "_id" : ObjectId("512118a7c1eca3d7ffcd00f9"), "name" : "Denis", "age" : null }

In order to return the document where the field does not exist, you can use $exists: false

db.Blog.find( { age: { $exists: false } } )

Here is the output

{ "_id" : ObjectId("512123d9c1eca3d7ffcd00fa"), "name" : "Denis2" }

That is all for this post, if you are interested in my other MongoDB posts, you can find them here:

Install MongoDB as a Windows Service

UPSERTs with MongoDB

How to sort results in MongoDB

Indexes in MongoDB: A quick overview

Multidocument updates with MongoDB

MongoDB: How to include and exclude the fields you want in results

MongoDB: How to limit results and how to page through results

MongoDB: How to backup and restore databases

MongoDB: How to restore collections

MongoDB: How to backup all the databases with one command

MongoDB: Exporting data into files

MongoDB: How to drop databases and collections

MongoDB: Creating capped collections