MongoDB Missing Fields Versus Null

In a relational table every row has every column, so “no value” has one spelling: NULL. A MongoDB document can either have a field set to null or not have the field at all. Those are two different states, and MongoDB’s query language treats them as the same in some places and different in others. Interviewers like this topic because it produces wrong counts that nobody notices until a report disagrees with another report.

The examples are mongosh for MongoDB 7 and 8. They weren’t run on a live server while writing; results are illustrative of documented behaviour.

Three documents, three states

db.users.insertMany([
  { _id: 1, name: "Ana",  phone: "+351 912 000 111" },
  { _id: 2, name: "Ben",  phone: null },
  { _id: 3, name: "Cleo" }
])

User 1 has a phone. User 2 has a phone field explicitly set to null, perhaps because a form submitted an empty value or someone cleared it. User 3 has no phone field, perhaps because the document was created before phones were collected.

Now query for users without a phone:

db.users.find({ phone: null })
// { _id: 2, name: "Ben",  phone: null }
// { _id: 3, name: "Cleo" }

Both come back. An equality match against null matches the null value and a missing field. That’s usually what you want when asking “who has no phone”, and it surprises people who expected only Ben.

Telling them apart

When the difference matters, use $exists or $type:

db.users.find({ phone: { $exists: false } })
// { _id: 3, name: "Cleo" }                 missing only

db.users.find({ phone: { $type: "null" } })
// { _id: 2, name: "Ben", phone: null }     explicit null only

db.users.find({ phone: { $exists: true } })
// _id 1 and _id 2                           field present, even if null

That last one catches people out. $exists: true is about the key, not the value, so Ben’s null phone counts as existing. If you meant “has a usable phone”, that’s the next section.

The negations

db.users.find({ phone: { $ne: null } })
// { _id: 1, name: "Ana", phone: "+351 912 000 111" }

$ne: null is the logical opposite of { phone: null }, so it excludes both the null and the missing documents. It reads as “has a real value”, which is the useful question most of the time.

Be careful with $nin and with negating other values. { status: { $ne: "banned" } } returns documents where status is anything else including missing. If half your older users have no status field, they all count as “not banned”. That’s often correct, but if you meant “has a status and it isn’t banned”, you need { status: { $exists: true, $ne: "banned" } }. A good habit when writing a negated filter is to ask yourself, “what about documents without this field?”

One more detail for MongoDB 8.0: equality matches against null no longer match the deprecated BSON undefined type. You’d only see undefined in very old data written by old drivers, but if a migration from an ancient system suddenly changes a count after upgrading, that’s a place to look.

Sorting, grouping and indexes

Missing and null also behave the same way in a few other places:

• Sort. In ascending sorts, missing fields and nulls come before numbers and strings. A “sort by last login ascending” shows never-logged-in users first, which may not be what the UI wants.
• Group. $group: { _id: "$phone" } puts null and missing in the same null bucket.
• Indexes. A regular index stores missing fields as null keys, so { phone: null } can use an index on phone. A sparse index skips documents that lack the field (but still indexes explicit nulls), so the planner won’t use a sparse index for { phone: null }, because it would miss the documents without the field.
• Unique indexes. Missing counts as null there too. A unique index on email allows only one document without an email. The fix is a partial index with partialFilterExpression: { email: { $exists: true } }, or better, { email: { $type: "string" } }, which also keeps explicit nulls out of the uniqueness check.

In aggregation expressions ($expr, $project), the rules differ slightly: $ifNull treats missing and null alike, while $type returns "missing" for an absent field and "null" for null. That’s a handy way to count the two states in a data-quality check:

db.users.aggregate([
  { $group: { _id: { $type: "$phone" }, n: { $sum: 1 } } }
])
// { _id: "string", n: 1 }, { _id: "null", n: 1 }, { _id: "missing", n: 1 }

Pick one meaning and enforce it

Most of these surprises go away if your application uses one representation consistently. Two common conventions:

• Omit the field when there’s no value. Smaller documents, and $exists checks map directly to “has value”. Use $unset to clear a value rather than setting it to null.
• Always write the field, null when unknown. Every document has the same shape, which some teams find easier for analytics exports.

Either works. Mixing them by accident is what causes bugs: one code path does $set: { phone: null }, another does $unset, and a report using $exists counts them differently. If null means something specific in your domain (for example, “user explicitly declined to give a phone” versus “we never asked”), then the two states carry information, and you should document it and query with $type and $exists precisely. A schema validator can enforce whichever convention you choose, for instance by allowing bsonType: ["string", "null"] or only "string".

Interview practice

A marketing report counts “users who opted out of email” with db.users.countDocuments({ emailOptIn: { $ne: true } }) and gets 1.2 million, far higher than expected. The emailOptIn field was added two years ago; older users don’t have it. New users get true or false. What went wrong, and how would you fix the query and the data?

Answer

$ne: true matches every document where emailOptIn isn’t true, and that includes documents where the field is missing. So the 1.2 million is real opt-outs (false) plus every pre-existing user who was never asked. The report mixes “said no” with “unknown”.

The immediate query fix depends on what the report should mean. For explicit opt-outs only:

db.users.countDocuments({ emailOptIn: false })

For a breakdown, group on the type:

db.users.aggregate([
  { $group: { _id: { $ifNull: ["$emailOptIn", "unknown"] }, n: { $sum: 1 } } }
])

For the data, I’d decide with the business what older users’ status should be. For consent, legally it’s usually “not opted in” unless there’s a record, which might mean storing that explicitly as false with a consentSource: "legacy" field rather than leaving it missing. Then backfill with updateMany({ emailOptIn: { $exists: false } }, { $set: { ... } }) in batches, and add a validator requiring emailOptIn to be a boolean so new code can’t reintroduce the missing state.

Further reading

• MongoDB interview questions
• MongoDB MCQs
• MongoDB Partial Indexes and Subset Queries
• MongoDB Schema Validation and Flexible Records
• Official docs: Query for null or missing fields