Partial, Sparse, Unique, and TTL Indexes

The exact operator set, null-vs-missing semantics, and the sparse+unique and async-deletion fixes

Partial supports $or/$in toonot just equality and comparison
Sparse excludes absence, not nullan explicit null is still indexed
unique + sparsethe fix for an optional unique field
TTL: expiry ≠ deletion timecheck timestamps for exact-time logic

1.partialFilterExpression: what it accepts, and sizing the reduction

partialFilterExpression accepts equality, $exists, comparison ($gt/$gte/$lt/$lte), $type, $and, $or, $in, and the geospatial $geoWithin/$geoIntersects operators — a real but not unlimited subset of the query language ($regex and several others are not supported). Size reduction is worth measuring, not assuming: count how many documents actually match the filter versus the full collection.

Key features

Supported: equality/$eq, $exists: true, $gt/$gte/$lt/$lte, $type, $and, $or, $in, $geoWithin, $geoIntersects
Not every query operator is supported — check the current manual before relying on one not listed here
The filter is evaluated once per document at write/index-build time, not re-evaluated per query
Size reduction ≈ 1 − (matching document count ÷ total document count) — measure it, do not assume it
A partial index with a filter matching most of the collection saves little size for the added complexity

Command

text

{ partialFilterExpression: { <field>: { $exists: true } } }  // equality, comparison, $and/$or/$in also supported

partialFilterExpression supports equality, $exists, comparison operators, $type, $and, $or, $in, and geospatial operators — check the current manual before assuming an operator is unsupported. Measure the real size reduction (matching vs. total document count) rather than assuming it.

2.What "missing" means for a sparse index

A sparse index excludes a document only if the indexed field is entirely absent — a field explicitly set to null still counts as present and is included. On a compound sparse index, a document is included if any one of the indexed fields is present, not only if all of them are.

Key features

Sparse excludes only truly absent fields — a field explicitly set to null is present and gets indexed
On a single-field sparse index, "missing" means the key does not appear in the document at all
On a compound sparse index, a document is included if at least one of the indexed fields is present
This differs from a partial index with { field: { $exists: true } }, which behaves the same way for a single field but is more general for multiple fields
Sparse is now considered the narrower, older mechanism — MongoDB's own docs recommend partial indexes for new designs

Command

text

db.<collection>.createIndex({ <field>: 1 }, { sparse: true })

Sparse excludes only truly absent fields — an explicit null still counts as present and is indexed. On a compound sparse index, any one present field is enough to include the document.

3.Unique indexes and missing fields, plus the sparse+unique fix

A missing field counts as null for a unique index, so only one document may omit it — section 10 covers this in depth. The fix for a genuinely optional unique field: combine unique: true with sparse: true, so documents missing the field are excluded from the uniqueness check entirely.

Key features

A plain unique index treats a missing field as null — only one document may omit it (full detail: section 10)
unique: true + sparse: true together: documents missing the field are excluded from the index, and therefore from the uniqueness check
This is the standard fix for "this field is optional, but must be unique among documents that have it"
A partial index with unique: true and an explicit $exists: true filter achieves the same result, more generally
Without sparse (or a partial filter), a second document omitting the field always collides on the shared null

Command

text

db.<collection>.createIndex({ <field>: 1 }, { unique: true, sparse: true })

unique alone treats missing as null (only one document may omit the field — see section 10). unique + sparse excludes missing-field documents from the index entirely, fixing the collision for genuinely optional unique fields.

4.TTL deletion is asynchronous, not exact-time

A TTL-expired document is not gone the instant its time is up — it becomes eligible for deletion, and a background task removes it on its own schedule (roughly every 60 seconds, per section 10). Anything downstream that assumes "expired means already deleted" can observe a document for up to about a minute past its expiry.

Key features

Expiry time and deletion time are two different moments — the gap is the async part (full mechanism: section 10)
A direct read of the collection can return an expired-but-not-yet-deleted document during that gap
Change streams (section 31) do emit a delete event when the TTL sweep actually removes the document — that event is not early
Application logic that needs exact-time behavior (e.g. "this discount code stops working at time T") should check the timestamp itself, not rely on the document's absence
This is the same caveat section 10 already states — repeated here because the roadmap calls it out as its own checkable fact

Command

text

// Read expiry-sensitive documents by checking the timestamp field directly, not by assuming TTL has already removed them

TTL deletion happens on the background sweep's own schedule, not the instant a document expires — application logic needing exact-time correctness must check the expiry timestamp itself, never assume absence-so-far means still valid.

Spotted a mistake on this page? Tell us.

Read Sheet