Null vs Missing Fields: Query Semantics, Index Behavior, and Data-Quality Consequences

Learning outcomes

AtlasMart has three different business states for a promotion code: a legacy record where the field was never written, a record where the application explicitly stored null to mean “known to have no code,” and a record containing a real string. A filter that treats those states as identical can corrupt migration metrics, send the wrong notifications, or create incorrect sparse/partial-index assumptions. MongoDB's query language gives you the tools to distinguish them—but only if you ask the precise question.

1. Equality to null means “explicit null or missing,” not “only BSON Null”

The query {promoCode:null} intentionally matches two cases: a document where promoCode exists and is BSON Null, and a document where promoCode does not exist. This is convenient when an application truly treats both states as “no value,” but dangerous when field presence itself carries meaning.

db.query_products.drop()db.query_products.insertMany([ {_id:"explicit-null",promoCode:null}, {_id:"missing"}, {_id:"string",promoCode:"SAVE10"}, {_id:"empty-string",promoCode:""}, {_id:"number",promoCode:10}])printjson({ equalityNull: db.query_products.find({promoCode:null},{_id:1,promoCode:1}).sort({_id:1}).toArray(), explicitNullOnly: db.query_products.find({promoCode:{$type:"null"}},{_id:1,promoCode:1}).sort({_id:1}).toArray(), missingOnly: db.query_products.find({promoCode:{$exists:false}},{_id:1}).sort({_id:1}).toArray(), presentNonNull: db.query_products.find({promoCode:{$ne:null}},{_id:1,promoCode:1}).sort({_id:1}).toArray()})

The expected sets are intentionally different. $type:"null" asks for the BSON Null type specifically. $exists:false asks for absence. $ne:null matches documents where the field exists and has a non-null value. Empty string and zero-like numeric values are not null; do not collapse them in application serializers.

2. Aggregation $type can reveal “missing” as data-quality evidence

The query $type operator filters by type. The aggregation $type expression returns a type-name string. When its field-path argument is absent, the expression returns "missing". This makes it useful in audits where you want to classify the dataset rather than just filter one category.

printjson(db.query_products.aggregate([ {$project:{_id:1,promoCode:1,promoType:{$type:"$promoCode"}}}, {$sort:{_id:1}}]).toArray())

That output can drive a migration report: how many records are missing because they predate the field, how many intentionally store null, and how many have unexpected legacy types. The report should inform a schema-evolution decision, not become an excuse to tolerate arbitrary types forever.

3. MongoDB 8.0 changed deprecated undefined/null comparison behavior

BSON undefined is deprecated. Before MongoDB 8.0, equality comparisons to null could also match undefined values or array elements. Starting in MongoDB 8.0, equality-to-null no longer matches deprecated undefined values. The modern rule taught in this course is therefore: null equality covers BSON Null and missing fields, but not deprecated undefined.

The lab does not create new undefined values because teaching a deprecated representation as a normal fixture would encourage the wrong pattern. The lesson records the compatibility boundary so learners can diagnose old datasets without reproducing obsolete writes.

4. Null predicates and indexes: “uses an index” is not the same as “covered”

A normal single-field index can participate in null-related queries, but MongoDB's query-optimization documentation states that a query containing a predicate equal to null cannot be a covered query. A covered query returns results entirely from index entries without examining collection documents. Because null equality includes missing-field semantics, the engine may need document evidence beyond a simple index key.

db.query_products.createIndex({promoCode:1})for (const [label,q] of [["null-or-missing",{promoCode:null}],["explicit-null",{promoCode:{$type:"null"}}],["non-null",{promoCode:{$ne:null}}]]) { const e=db.query_products.explain("executionStats").find(q,{_id:0,promoCode:1}); printjson({label,nReturned:e.executionStats.nReturned,keys:e.executionStats.totalKeysExamined,  docs:e.executionStats.totalDocsExamined,winningPlan:e.queryPlanner.winningPlan});}

The exact plan can vary with dataset size, selectivity, engine, and cached plans. The production lesson is to inspect nReturned, keys/documents examined, and the winning plan for your workload. Sparse and partial indexes can encode presence/type assumptions, but they also change which documents are represented; later index lessons treat those designs explicitly.

5. AtlasMart null/missing data-quality lab

docker rm -f atlasmart-mongo-ch04-l3docker run --name atlasmart-mongo-ch04-l3 -p 127.0.0.1:27034:27017 -d mongodb/mongodb-community-server:8.3.8-ubuntu2204-slimdocker logs atlasmart-mongo-ch04-l3 --tail 25

mongosh "mongodb://127.0.0.1:27034/atlasmart?directConnection=true" --quiet --eval 'db.query_products.drop();db.query_products.insertMany([ {_id:"explicit-null",promoCode:null}, {_id:"missing"}, {_id:"string",promoCode:"SAVE10"}, {_id:"empty-string",promoCode:""}, {_id:"number",promoCode:10}]);db.query_products.createIndex({promoCode:1});const sets={ nullOrMissing:db.query_products.find({promoCode:null},{_id:1}).sort({_id:1}).toArray(), explicitNull:db.query_products.find({promoCode:{$type:"null"}},{_id:1}).sort({_id:1}).toArray(), missing:db.query_products.find({promoCode:{$exists:false}},{_id:1}).sort({_id:1}).toArray(), nonNull:db.query_products.find({promoCode:{$ne:null}},{_id:1}).sort({_id:1}).toArray(), types:db.query_products.aggregate([{$project:{_id:1,t:{$type:"$promoCode"}}},{$sort:{_id:1}}]).toArray()};printjson(sets);const e=db.query_products.explain("executionStats").find({promoCode:null},{_id:0,promoCode:1});printjson({nReturned:e.executionStats.nReturned,keys:e.executionStats.totalKeysExamined,docs:e.executionStats.totalDocsExamined});' 

Verification checklist

• {promoCode:null} returns both the explicit-null and missing fixtures.
• $type:"null" returns only the explicit BSON Null fixture.
• $exists:false returns only the missing-field fixture.
• $ne:null includes empty string and numeric values because they are present and non-null.
• The aggregation type report prints missing for the absent field.
• The explain output is treated as environment-specific evidence; the lesson does not claim a universal fixed stage tree.

docker rm -f atlasmart-mongo-ch04-l3

The next lesson uses $expr when the predicate itself must compute or compare values from the document.

Authoritative references

• MongoDB release notes — Official current stable server series and patch notes.
• MongoDB 8.3 release notes — Official 8.3 patch history; 8.3.8 is the latest released patch at review time.
• MongoDB query documents — Official find/query behavior and cursor semantics.
• MongoDB query optimization — Official selectivity, index, and explain guidance.
• PyMongo query documents — Official Python driver query-filter behavior.
• Query for null or missing fields — Official null/missing query semantics across drivers.
• MongoDB 8.0 compatibility changes — MongoDB 8.0 change: null equality no longer matches deprecated undefined values.
• Migrate undefined data and queries — Official migration guidance for deprecated BSON undefined.
• $type aggregation expression — Returns BSON type strings and the special missing result for absent fields.
• Query optimization — Covered-query requirements and null predicate limitation.