Unique & Partial Indexes — MongoDB

Unique and Partial Indexes

Not every index is about speeding up reads. Some indexes exist purely to enforce a constraint — that a value must never repeat, or that a constraint only applies to a subset of documents. MongoDB gives you two tools for this: unique indexes and partial indexes, and the two combine into one of the most useful patterns in schema design.

Unique Indexes

A unique index guarantees that no two documents in a collection share the same value for the indexed field (or field combination). MongoDB checks the constraint on every insert and update, and rejects the write with a duplicate key error if it would violate uniqueness.

Creating a unique index

// Single-field unique index
db.users.createIndex({ email: 1 }, { unique: true })

// Compound unique index — the COMBINATION must be unique,
// not each field individually
db.enrollments.createIndex(
  { studentId: 1, courseId: 1 },
  { unique: true }
)
// A student can enroll in many courses, and a course can have
// many students, but the SAME student cannot enroll in the
// SAME course twice.

If you try to insert a document that violates the constraint, MongoDB throws error code 11000:

db.users.insertOne({ email: "alice@example.com", name: "Alice" })
db.users.insertOne({ email: "alice@example.com", name: "Alice 2" })

MongoServerError: E11000 duplicate key error collection: shop.users
index: email_1 dup key: { email: "alice@example.com" }

Unique Indexes and Null Values

This is the single most common gotcha with unique indexes: MongoDB treats a missing field and an explicit null as the same value for index purposes. That means a unique index allows exactly one document with a missing or null value for that field — every subsequent one collides.

db.users.createIndex({ ssn: 1 }, { unique: true })

db.users.insertOne({ name: "Bob" })              // OK — ssn is missing
db.users.insertOne({ name: "Carol", ssn: null }) // FAILS — duplicate of missing/null
db.users.insertOne({ name: "Dave", ssn: null })  // FAILS too

Warning

A plain unique index treats a missing field and null as the same indexed value. If a field is optional, wrap the index with partialFilterExpression so the constraint only applies when the field actually exists.

Sparse Indexes — the Old Fix

Before partial indexes existed, the fix for "optional unique field" was a sparse index: it simply skips documents that don't have the indexed field at all.

db.users.createIndex(
  { ssn: 1 },
  { unique: true, sparse: true }
)
// Documents WITHOUT an ssn field are excluded from the index entirely.
// Multiple documents can now omit ssn without colliding.
// But: a document with ssn: null still gets indexed (null is a value).

Note

Sparse indexes only look at whether the field exists. Partial indexes (below) can filter on any expression, and are almost always the better choice today — MongoDB's own docs recommend partial indexes over sparse indexes for new applications.

Partial Indexes

A partial index only indexes documents that match a specified filter expression (partialFilterExpression). This has two big benefits: the index is smaller and cheaper to maintain, and it lets you scope a unique constraint to a subset of your data — exactly the "optional unique field" problem above, solved properly.

Partial index — index only active documents

// Only index documents where status is "active" — a common
// pattern for large collections where most queries filter on status
db.orders.createIndex(
  { customerId: 1, createdAt: -1 },
  { partialFilterExpression: { status: "active" } }
)

// This query CAN use the index (matches the filter):
db.orders.find({ customerId: "u1", status: "active" }).sort({ createdAt: -1 })

// This query CANNOT use the index (status not in the filter):
db.orders.find({ customerId: "u1" }).sort({ createdAt: -1 })

The Classic Combo: Unique + Partial

The pattern that makes partial indexes essential: soft-deleted users with a reusable email. When a user "deletes" their account you keep the row (deletedAt set) but you want to let a new signup reuse that email address, while still preventing two active accounts from sharing an email.

Unique email, only among active accounts

db.users.createIndex(
  { email: 1 },
  {
    unique: true,
    partialFilterExpression: { deletedAt: { $exists: false } }
  }
)

// Alice signs up — no deletedAt field yet, so this document
// is covered by the partial index
db.users.insertOne({ email: "alice@example.com" })

// Alice deletes her account — deletedAt is now set, so this
// document falls OUT of the partial index
db.users.updateOne(
  { email: "alice@example.com" },
  { $set: { deletedAt: new Date() } }
)

// A new user can now register with the same email — no conflict,
// because the old document no longer matches the partial filter
db.users.insertOne({ email: "alice@example.com" })

Tip

Design the field so "not deleted" means the field is truly absent (never set), and only appears once a document is soft-deleted. Then { deletedAt: { $exists: false } } cleanly separates active from deleted documents in the partial filter.

Other Common Partial Index Filters

// Only index documents above a threshold
db.products.createIndex(
  { sku: 1 },
  { partialFilterExpression: { inStock: { $gt: 0 } } }
)

// Only index a specific document "shape" (useful with polymorphic
// collections — see the Schema Patterns page)
db.events.createIndex(
  { userId: 1, occurredAt: -1 },
  { partialFilterExpression: { type: "purchase" } }
)

// Combine with unique to enforce "one open ticket per user"
db.tickets.createIndex(
  { userId: 1 },
  { unique: true, partialFilterExpression: { status: "open" } }
)

Partial Index Filter Expression Limits

Supports equality expressions, $exists: true, $gt/$gte/$lt/$lte, $type, and top-level $and of these.
Does not support $or, $nor, $ne, or $in in the filter expression.
The query must include the filter conditions (or a superset the planner can prove implies them) for MongoDB to consider using the partial index.
A _id index can never be partial — it always covers every document.

Sparse vs Partial — Quick Comparison

Aspect	Sparse Index	Partial Index
Filter logic	Field must exist	Any supported expression
Unique + optional field	Works, but crude	Works, and precise
Can combine with any query shape	No	Yes, more flexible
Recommended for new code	No	Yes
`_id` field	Cannot be sparse	Cannot be partial

Note

You cannot use both sparse and partialFilterExpression on the same index — pick one. Partial indexes are the modern, more expressive choice.

Checking Which Index Was Used

db.orders.find({ customerId: "u1", status: "active" })
  .sort({ createdAt: -1 })
  .explain("executionStats")
// Look at winningPlan.inputStage.indexName to confirm the
// partial index was chosen, and totalDocsExamined to see
// how much smaller the scan is versus a full index.

Summary

Unique index: rejects writes that duplicate an indexed value; missing/null count as one value.
Sparse index: unique index that ignores documents missing the field — the older, cruder tool.
Partial index: indexes only documents matching a filter expression — smaller, faster, and can carry a unique constraint scoped to a subset.
The unique + partial combo is the standard way to model "unique among active records, reusable once archived/deleted."