top of page
ClsoeIT Logo

Null Checking in MongoDB

When we started using MongoDB in our projects, I was very confused by aggregations. But I wrote so many of them at this point, I actually enjoy putting them together. At least I enjoy it more than writing SQL queries. Yet there is one thing I have to process for a while in my mind every time - how to do null checking and field existence checking.

This can be quite confusing especially when you're coming from relational databases.

If we make a connection between table and collection, we see similarities between rows - documents and columns - fields. The biggest difference is, that each document from same collection can contain very different set of fields. So not only the field can contain null, but it may also not be there. Sometimes, we want to differentiate between those. The way of doing checks also depends on the usage and what do you want to achieve.


The syntax of many operators differs when used in query or in aggregation. Null checking in query is rather simple. We can test any field against null. This query will find all accounts not only where field accountId is null, but also where the field doesn't exist.

db.accounts.find({ accountId : null })

To find accounts where accountId does exist and isn't null, we can use operator $ne - not equals. The second query with operator $eq is equivalent of query above.

db.accounts.find({ accountId : {$ne : null} })db.accounts.find({ accountId : {$eq : null} })

In case we want to check only existence and don't care about null, there is an operator $exists.

db.accounts.find({ accountId : { $exists: true} })db.accounts.find({ accountId : { $exists: false} })

Subtasks Navigation for Jira promotion Banner


The stage $match used to filter documents works exactly the same way as query.

{ $match: { accountId : null } }{ $match: { accountId : {$ne : null}} }{ $match: { accountId : {$eq : null}} }{ $match: { accountId : {$exists : true} } }{ $match: { accountId : {$exists : false} }

So far so good. Here comes one tricky point. In some cases, you don't want to exclude documents from pipeline but rather create a field with value which depends on the existence or null. Using the previous knowledge, we could put together Project stage with null test in condition.

{ $project: { accountIdFlag : {$cond : [{$eq : ["$accountId", null]}, 0, 1]} }}

My goal here is to create field accountIdFlag which will be 0 if accountId is null or non-existent and 1 if it contains value. However if you run this aggregation, the field will always be 1. We have to choose different strategy in this case.

{ $project: { accountIdFlag : {$cond : [{$eq : [{ $ifNull: ["$accountId", null]} , null]}, 0, 1]} }}

If the expression in $ifNull evaluates to null or missing field, replacement expression is returned - null. If not, the value is returned. We test the result against null and this time, we get correct flag - 0 if the field is null or missing and 1 if not.

At the end of the day, null checking isn't hard. But the use case above shows that it can be sometimes tricky so I hope this article will make it clear for those who struggles with null checks in MongoDB.

Subtasks Navigation for Jira promotion Banner

Related Posts

See All

Validating and generating Atlassian JWT

When developing a add-on to cloud Jira, a usual need is to communicate with the Jira using REST API. The authentication is done using JWT and it took us a while to figure out how to validate and gener

MGS integration with antivirus

One of the MGS features is to manage model-related files and documents. Of course, common and other model non-related files can be uploaded also to the public folder, users' private folder, or shared

Flattening Docker images

Docker images are stored as layers. To be more precise, the filesystem is layered, so each change (RUN, COPY,…) will result in adding a new layer. This approach has many advantages - images can be bui


bottom of page