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.

Query

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} })

Aggregation

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.