Convert SQL IS NULL / IS NOT NULL to MongoDB Null Queries

NULL Handling in MongoDB

SQL's IS NULL and IS NOT NULL have MongoDB equivalents, but with an important nuance: MongoDB distinguishes between a field that is null and a field that does not exist at all.

Example SQL

SELECT * FROM users
WHERE deleted_at IS NULL

Generated MongoDB Query

db.users.find({
  deleted_at: null
})

IS NOT NULL

SELECT * FROM users
WHERE email IS NOT NULL

db.users.find({
  email: { $ne: null }
})

MongoDB's NULL Semantics

The filter { field: null } matches documents where:

1. The field exists and its value is null
2. The field does not exist in the document

This is different from SQL where a column always exists (it is part of the schema) and IS NULL only checks for NULL values.

Checking Field Existence

If you specifically want to check whether a field exists (regardless of its value), use the $exists operator:

// Field exists (any value including null)
db.users.find({ avatar: { $exists: true } })

// Field does not exist
db.users.find({ avatar: { $exists: false } })

Combining NULL and Existence Checks

For precise control:

// Field exists AND is null
db.users.find({ field: { $exists: true, $eq: null } })

// Field exists AND is NOT null
db.users.find({ field: { $exists: true, $ne: null } })

In Practice

For most SQL-to-MongoDB migrations, the simple { field: null } and { field: { $ne: null } } patterns are sufficient because the application typically ensures all expected fields are present in documents.