How do you distinguish a missing field from one explicitly set to null in a MongoDB query?

middleshould knowfreq 55%

answer

Two absences, one equality rule
null equality is deliberately forgiving
Presence is a separate question from value
One operator tests the stored BSON type
Combine two conditions for a real value

basics

~20 s

A filter of { field: null } matches both cases. Use { field: { $exists: false } } for missing only, { field: { $type: "null" } } for an explicit null only, and { field: { $exists: true, $ne: null } } for present and non-null.

solid answer

~50 s

MongoDB treats an absent field and a field holding null as equal for equality matching, so { nickname: null } returns documents that never had the field and documents where it was stored as null — and, through element matching, arrays containing a null. To separate them you need $exists or $type. { nickname: { $exists: false } } selects only documents lacking the field; { nickname: { $exists: true } } selects those that have it, including an explicit null. { nickname: { $type: "null" } } selects only the explicit-null case, because $type tests the stored BSON type. Combine them for the common "has a real value" predicate: { nickname: { $exists: true, $ne: null } }. $type also accepts an array of aliases such as { $type: ["string", "int"] }, which is the practical tool for auditing a collection whose field types have drifted.

MongoDB treats an absent field and a field holding `null` as equal for equality matching, so `{ nickname: null }` returns documents that never had the field **and** documents where it was stored as `null` — and, through element matching, arrays containing a null. To separate them you need `$exists` or `$type`. `{ nickname: { $exists: false } }` selects only documents lacking the field; `{ nickname: { $exists: true } }` selects those that have it, including an explicit `null`. `{ nickname: { $type: "null" } }` selects only the explicit-null case, because `$type` tests the stored BSON type. Combine them for the common "has a real value" predicate: `{ nickname: { $exists: true, $ne: null } }`. `$type` also accepts an array of aliases such as `{ $type: ["string", "int"] }`, which is the practical tool for auditing a collection whose field types have drifted.

code

javascript · 6 lines

javascript

db.users.insertMany([{ _id: 1 }, { _id: 2, nickname: null }, { _id: 3, nickname: "kt" }])

db.users.find({ nickname: null })                        // _id 1 and 2
db.users.find({ nickname: { $exists: false } })           // _id 1
db.users.find({ nickname: { $type: "null" } })            // _id 2
db.users.find({ nickname: { $exists: true, $ne: null } }) // _id 3

go deeper

for a junior

Remember that a filter comparing a field to null matches both documents missing the field and documents storing null, and that $exists is the operator that separates presence from value.

for a middle

Explain all four predicates precisely — missing only, explicit null only, either, and present-with-a-value — and describe what $type tests, including its string aliases and list form.

for a senior

Bring the operational angle: audit type and presence drift across a live collection, and explain how sparse indexes interact with the missing-versus-null choice so read paths stay indexable.

for a principal

Set the policy — decide whether absent means null across the platform, get it enforced at write time, and weigh the storage and index consequences of always materialising nullable fields.

Two different absences

A document store has a distinction relational tables do not: a field can be absent from the document entirely, or present with the value null. Applications usually mean different things by them — "never supplied" versus "deliberately cleared" — and MongoDB's default matching rule collapses the two.

{ _id: 1 }
{ _id: 2, nickname: null }
{ _id: 3, nickname: "kt" }

db.users.find({ nickname: null }) returns documents 1 and 2. The rule is that equality against null matches documents where the field is null and documents where the field does not exist. This is deliberate: it makes queries robust against documents written before a field was introduced, which is a common state in a schemaless store. It is also the source of a steady stream of bugs when the two absences carry different business meaning.

$exists

$exists tests presence, not value. { nickname: { $exists: true } } matches documents 2 and 3 — presence, even with a null value, counts. { nickname: { $exists: false } } matches document 1 only.

$exists takes a boolean. Older habits of writing { $exists: 1 } work because of type coercion, but true/false is what to write. Note that $exists: true says nothing about the type or usefulness of the value; a field present with an empty string or an empty array still exists.

$type

$type tests the BSON type of the stored value and accepts string aliases ("string", "int", "long", "double", "bool", "date", "objectId", "array", "object", "null", "binData") or the corresponding numeric type codes. { nickname: { $type: "null" } } matches only document 2: the field must be present and its type must be the null type. This is the precise "explicitly cleared" predicate.

Two behaviours are worth memorising. First, $type accepts a list: { zip: { $type: ["string", "int"] } } matches either, which makes it the right tool for finding type drift — run { zip: { $type: "int" } } on a field that should always be a string and see what turns up. Second, when the field holds an array, $type matches if any element has the given type, and additionally matches the whole field when the requested type is "array". So { tags: { $type: "string" } } matches a document whose tags is ["a", 1], and { tags: { $type: "array" } } matches because the field itself is an array.

The numeric alias "number" is a convenience that covers the integer, long, double and decimal types together, which is what you usually want when checking that a field is numeric at all.

The four predicates you actually need

Missing only: { f: { $exists: false } }
Explicit null only: { f: { $type: "null" } }
Missing or null: { f: null }
Present with a real value: { f: { $exists: true, $ne: null } }

The last one is the one most applications want and the one most often written wrongly as { f: { $ne: null } } — which is subtly different, since $ne: null alone matches documents where the field is missing? No: $ne: null is the negation of the equality that matched both cases, so it excludes both missing and null and is in fact equivalent to the combined form for this specific operand. Writing $exists: true alongside costs nothing and states the intent to the next reader.

Modelling consequences

If your code cares about the difference, the safest strategy is to make it not exist: pick one representation and enforce it. Either always store the field (using null for "unknown") or never store it until it has a value, and validate that at write time. Otherwise every read path must carry both predicates, and every developer must remember the collapsing rule.

There is also an index consequence. A sparse index skips documents that lack the indexed field, so a query that must return missing-field documents cannot be served by one; a document with the field explicitly set to null is indexed by a sparse index, because the field exists. Choosing one representation therefore has a direct effect on which indexes can answer which queries.

Auditing drift

Because any document may omit any field, a periodic audit is worth having: for each field you rely on, count documents where { f: { $exists: false } } and where { f: { $type: "null" } }, and where the type is anything other than expected. That triple is the schemaless equivalent of a NOT NULL constraint report.

## Two different absences A document store has a distinction relational tables do not: a field can be absent from the document entirely, or present with the value `null`. Applications usually mean different things by them — "never supplied" versus "deliberately cleared" — and MongoDB's default matching rule collapses the two. ``` { _id: 1 } { _id: 2, nickname: null } { _id: 3, nickname: "kt" } ``` `db.users.find({ nickname: null })` returns documents 1 and 2. The rule is that equality against `null` matches documents where the field is null and documents where the field does not exist. This is deliberate: it makes queries robust against documents written before a field was introduced, which is a common state in a schemaless store. It is also the source of a steady stream of bugs when the two absences carry different business meaning. ## $exists `$exists` tests presence, not value. `{ nickname: { $exists: true } }` matches documents 2 and 3 — presence, even with a null value, counts. `{ nickname: { $exists: false } }` matches document 1 only. `$exists` takes a boolean. Older habits of writing `{ $exists: 1 }` work because of type coercion, but `true`/`false` is what to write. Note that `$exists: true` says nothing about the type or usefulness of the value; a field present with an empty string or an empty array still exists. ## $type `$type` tests the BSON type of the stored value and accepts string aliases (`"string"`, `"int"`, `"long"`, `"double"`, `"bool"`, `"date"`, `"objectId"`, `"array"`, `"object"`, `"null"`, `"binData"`) or the corresponding numeric type codes. `{ nickname: { $type: "null" } }` matches only document 2: the field must be present and its type must be the null type. This is the precise "explicitly cleared" predicate. Two behaviours are worth memorising. First, `$type` accepts a list: `{ zip: { $type: ["string", "int"] } }` matches either, which makes it the right tool for finding type drift — run `{ zip: { $type: "int" } }` on a field that should always be a string and see what turns up. Second, when the field holds an array, `$type` matches if any element has the given type, and additionally matches the whole field when the requested type is `"array"`. So `{ tags: { $type: "string" } }` matches a document whose `tags` is `["a", 1]`, and `{ tags: { $type: "array" } }` matches because the field itself is an array. The numeric alias `"number"` is a convenience that covers the integer, long, double and decimal types together, which is what you usually want when checking that a field is numeric at all. ## The four predicates you actually need - **Missing only**: `{ f: { $exists: false } }` - **Explicit null only**: `{ f: { $type: "null" } }` - **Missing or null**: `{ f: null }` - **Present with a real value**: `{ f: { $exists: true, $ne: null } }` The last one is the one most applications want and the one most often written wrongly as `{ f: { $ne: null } }` — which is subtly different, since `$ne: null` alone matches documents where the field is missing? No: `$ne: null` is the negation of the equality that matched both cases, so it excludes both missing and null and is in fact equivalent to the combined form for this specific operand. Writing `$exists: true` alongside costs nothing and states the intent to the next reader. ## Modelling consequences If your code cares about the difference, the safest strategy is to make it not exist: pick one representation and enforce it. Either always store the field (using `null` for "unknown") or never store it until it has a value, and validate that at write time. Otherwise every read path must carry both predicates, and every developer must remember the collapsing rule. There is also an index consequence. A sparse index skips documents that lack the indexed field, so a query that must return missing-field documents cannot be served by one; a document with the field explicitly set to `null` *is* indexed by a sparse index, because the field exists. Choosing one representation therefore has a direct effect on which indexes can answer which queries. ## Auditing drift Because any document may omit any field, a periodic audit is worth having: for each field you rely on, count documents where `{ f: { $exists: false } }` and where `{ f: { $type: "null" } }`, and where the type is anything other than expected. That triple is the schemaless equivalent of a NOT NULL constraint report.

Does { nickname: { $exists: true } } match a document where nickname is null?
Yes. $exists tests presence only, so a field stored as null exists and matches $exists: true. It says nothing about the value being useful — an empty string, an empty array and null all count as present. If you need presence plus a real value, write { nickname: { $exists: true, $ne: null } }, and if you need the explicit-null case specifically, use { nickname: { $type: "null" } }.
How does $type behave when the field holds an array?
It matches if any element has the requested type, so { tags: { $type: "string" } } matches a document whose tags is ["a", 1]. Additionally, requesting "array" matches when the field itself is an array. This makes $type useful for auditing arrays with mixed element types, but it means a positive match does not prove every element has that type.
Why does a sparse index interact with this distinction?
A sparse index contains entries only for documents where the indexed field is present. A document with the field set to null is present, so it is indexed; a document lacking the field is not. A query for missing-field documents therefore cannot be answered from that index. Choosing one representation for absence — always store null, or never store the field — decides which of your queries stay indexable.

saying these in an interview costs you the question

Says { f: null } matches only explicit nulls
Believes $exists checks whether the value is non-empty
Thinks a missing field is an error rather than normal
Uses $type with an invented alias name
Assumes $type on an array requires every element to match

How do you distinguish a missing field from one explicitly set to null in a MongoDB query?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Two different absences

$exists

$type

The four predicates you actually need

Modelling consequences

Auditing drift

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions6 questions

▾related topics4 topics

Two different absences

$exists

$type

The four predicates you actually need

Modelling consequences

Auditing drift

answer

go deeper

context