Choose partial or sparse membership rules deliberately, prove query eligibility, and expose incomplete-result risks with null/missing/type-drift fixtures.

Partial vs Sparse Indexes: Selective Indexing and Query Eligibility

Batch heterogeneous writes safely, interpret partial success, compare ordered and unordered execution, and use modern cross-namespace bulk APIs without assuming all-or-nothing behavior.

Intermediate105–145 minutesPartial/sparse eligibility + null/missing labMongoDB 8.3.8 · mongosh 2.10.0Last reviewed: September 2026

Learning objectives

Separate sparse inclusion semantics from the more expressive partialFilterExpression contract.

Prove that explicit null is indexed by an ordinary sparse scalar index while a missing field is not.

Determine when a query logically implies a partial-index filter and is therefore eligible for complete results.

Use explain, index metadata, and controlled hint experiments to expose incomplete-index risks.

Choose selective indexes from invariants and workload rather than treating sparse and partial as synonyms.

Reproducible lab baseline

This lesson pins MongoDB Community Server 8.3.8 with mongodb/mongodb-community-server:8.3.8-ubuntu2204-slim and mongosh 2.10.0. The server is a disposable standalone published only on loopback 127.0.0.1:27067. Authentication and TLS are disabled only for this isolated lab. Feature Compatibility Version (FCV) is observed but never changed. Default read/write concern and primary read preference apply. Atlas, KMS, and Enterprise Advanced are not mandatory. Runtime output shown as “expected” is documentation-derived because this generation environment has no Docker/mongod/mongosh runtime.

Evidence before index enthusiasm

Specialized indexes are useful only when their eligibility rules match the workload. Every lab therefore inspects index metadata, matching and non-matching query shapes, explain evidence, and state transitions. Tiny fixtures prove semantics—not production latency, cache behavior, or sharded-cluster distribution.

1. AtlasMart problem: optional fields and active-only workloads

AtlasMart product documents evolve over time. A promotion code is optional, and only active products with a numeric sale price are queried by the “discount finder” endpoint. A sparse index decides membership from field presence. A partial index decides membership from an explicit filter expression and can filter on fields that are not themselves index keys. Those are different semantics, and confusing them can silently produce incomplete answers.

Mechanism	Indexed membership	Typical reason
Sparse `{promoCode:1}`	Documents where `promoCode` exists; explicit `null` still counts as present.	Optional field queried only when present.
Partial `{tenantId,salePriceCents}`	Only documents satisfying `status:"active"` and numeric price.	Reduce index size/write cost for an operational subset.
Non-sparse ordinary index	All documents, including a null index representation for missing scalar fields.	Queries may need complete collection coverage.

Hard boundary

You cannot combine sparse:true and partialFilterExpression on the same index. Use partial filtering when the membership rule needs more than “field exists.”

2. Seed deliberately mixed documents

bash · isolated Chapter 11 Lesson 1 lab setup

docker rm -f atlasmart-mongo-ch11-l1 2>/dev/null || truedocker volume rm atlasmart-mongo-ch11-l1-data 2>/dev/null || truedocker run -d --name atlasmart-mongo-ch11-l1 \  -p 127.0.0.1:27067:27017 \  -v atlasmart-mongo-ch11-l1-data:/data/db \  mongodb/mongodb-community-server:8.3.8-ubuntu2204-slimmongosh "mongodb://127.0.0.1:27067/atlasmart?directConnection=true" --quiet --eval \'printjson({server:db.version(),hello:db.hello().isWritablePrimary}); printjson(db.getSiblingDB("admin").runCommand({getParameter:1,featureCompatibilityVersion:1}))'

javascript · seed active, archived, null, missing, and type-drift cases

const c=db.catalog_ch11_l1;c.drop();c.insertMany([ {_id:1,tenantId:"tenant-a",sku:"A-1",status:"active",promoCode:"SAVE10",salePriceCents:2500}, {_id:2,tenantId:"tenant-a",sku:"A-2",status:"active",promoCode:null,salePriceCents:3900}, {_id:3,tenantId:"tenant-a",sku:"A-3",status:"active",salePriceCents:1800}, {_id:4,tenantId:"tenant-a",sku:"A-4",status:"archived",promoCode:"OLD",salePriceCents:1500}, {_id:5,tenantId:"tenant-a",sku:"A-5",status:"active",promoCode:"VIP",salePriceCents:"1999"}, {_id:6,tenantId:"tenant-b",sku:"B-1",status:"active",promoCode:"SAVE10",salePriceCents:2300}, {_id:7,tenantId:"tenant-b",sku:"B-2",status:"archived",salePriceCents:1200}, {_id:8,tenantId:"tenant-a",sku:"A-6",status:"active",promoCode:"FLASH",salePriceCents:5200}]);printjson(c.find({},{_id:0,tenantId:1,sku:1,status:1,promoCode:1,salePriceCents:1}).sort({sku:1}).toArray());

3. Build one sparse and one partial index

The partial filter is intentionally stricter than field existence: it requires active status and a numeric BSON type. That prevents the historical string price from entering the index.

javascript · create selective indexes and inspect metadata

const c=db.catalog_ch11_l1;print(c.createIndex({promoCode:1},{name:"idx_promo_sparse",sparse:true}));print(c.createIndex( {tenantId:1,salePriceCents:1}, {name:"idx_active_numeric_sale",partialFilterExpression:{status:"active",salePriceCents:{$type:"number"}}}));printjson(c.getIndexes());printjson(c.stats().indexSizes);

4. Query eligibility is a correctness rule

MongoDB does not select a partial index when doing so would omit documents that the query is allowed to match. Therefore, an eligible query must include the filter expression or a logically narrower condition. Sparse indexes have a similar completeness concern: MongoDB avoids them for a query/sort that needs missing-field documents unless you explicitly force the index.

javascript · compare eligible and ineligible shapes

const c=db.catalog_ch11_l1;const cases=[ ["partial eligible",  {tenantId:"tenant-a",status:"active",salePriceCents:{$type:"number",$lt:3000}},  "idx_active_numeric_sale"], ["partial not logically complete",  {tenantId:"tenant-a",salePriceCents:{$lt:3000}},  null], ["sparse existence query",  {promoCode:{$exists:true}},  "idx_promo_sparse"], ["all documents sorted by promoCode",  {},  null]];for(const [label,filter,hint] of cases){ print(`--- ${label} ---`); let q=c.find(filter,{_id:0,sku:1,status:1,promoCode:1,salePriceCents:1}); if(hint) q=q.hint(hint); printjson(q.explain("executionStats")); printjson(q.toArray());}

text · deterministic semantic expectations

Expected semantic facts (exact plan formatting may vary):- Total documents: 8.- promoCode exists in documents 1, 2, 4, 5, 6, 8 -> sparse index includes explicit null in document 2.- Documents 3 and 7 omit promoCode -> they are absent from idx_promo_sparse.- The partial index includes only status:"active" documents whose salePriceCents BSON type is numeric.- The string price in A-5 is excluded from the partial index even though the document is active.- A query that does not imply the partial filter cannot safely use the partial index for complete results.- Forcing a sparse index for an all-document count can return an incomplete count by design.

5. Deliberately wrong: force sparse coverage for an all-document count

A practitioner might reason “promoCode is indexed, so counting through that small index must be faster.” That changes the question. The sparse index does not contain documents where promoCode is missing, so a forced count observes only the indexed subset.

javascript · controlled incomplete-result demonstration

const c=db.catalog_ch11_l1;print("Correct collection count:",c.countDocuments({}));print("Intentionally forced sparse-index count:",c.countDocuments({}, {hint:"idx_promo_sparse"}));print("Documents that actually have promoCode:",c.countDocuments({promoCode:{$exists:true}}));

Repair

Use an unhinted complete count when the business question is “all documents.” Use a sparse/partial index only when the query predicate itself constrains results to the index membership contract. Hints are useful here as diagnostics precisely because they can expose how an incomplete specialized index would behave.

6. Storage/write cost and production judgment

Selective indexes can be smaller and cheaper to maintain because fewer documents produce index entries. That does not make them free: every qualifying insert/update can modify the index, and transitions into or out of the filter membership also change it. On replica sets the index definition and maintenance burden exist on members; on sharded collections, shard-key and partial-index restrictions must be checked before design. If an authorization or tenant invariant relies on a filter, enforce authorization separately—the presence of a tenant field in an index is not a security boundary.

Verification checklist

Eight fixture documents exist.
The sparse index includes explicit null but excludes missing promoCode.
The partial index excludes archived documents and the string-typed price.
The eligible query can be explained against the partial index without losing valid results.
The broader price query is not claimed to be covered by that partial membership contract.
The forced sparse count is explicitly identified as an incomplete answer to an all-document question.

Production judgment. Use sparse indexes only when presence-based membership is exactly the desired contract. Prefer partial indexes when business state, type, date, or another predicate controls eligibility. Record the filter with the application query contract, observe index size and write cost, test historical type drift, and make retirement reversible through hide/observe/drop procedures. Lesson 2 applies the same “index membership is semantics” idea to data retention, where the consequence is deletion rather than query eligibility.

bash · cleanup / full reset

docker rm -f atlasmart-mongo-ch11-l1docker volume rm atlasmart-mongo-ch11-l1-data

Check your understanding

Does a sparse index exclude a field whose value is null?
What must a query do before MongoDB can safely use a partial index?
Can partialFilterExpression reference a field that is not an index key?
Why is forcing a sparse index for an empty-predicate count dangerous?
When is partial preferable to sparse?

Review the answers

1. No. A sparse scalar index includes documents where the indexed field exists even when its value is explicit null; it skips documents where the field is missing.

2. Its predicate must include or logically imply a condition that is at least as restrictive as the partial filter so the result remains complete.

3. Yes. Partial membership can depend on fields other than the indexed keys.

4. The index omits missing-field documents, so forcing it can turn an “all documents” question into “documents present in this index.”

5. When membership needs explicit business/type/range logic rather than only field existence.

Authoritative references

MongoDB 8.3 release notes — Current 8.3 baseline and patch-sensitive behavior; re-check before reproduction.
Index types — Current index-family overview and boundaries.
Explain results — Planner and execution evidence used throughout this chapter.
mongosh changelog — mongosh version used for chapter commands.
Partial indexes — Membership filters, query eligibility, restrictions, and partial uniqueness.
Sparse indexes — Missing-versus-null membership and incomplete-result behavior.

Keep knowledge open

Help the academy stay free and grow.

If these tutorials save you time, a small donation supports new lessons, technical review, diagrams, examples, and long-term maintenance.

ETHEthereum / ERC-20 only

0x716c4Ab160C4B66F31a28AE2448BfF68fc3a2ef0

Send only Ethereum or ERC-20 compatible assets to this address.