5.4 Specialized Index Types: TTL, Partial, Unique & Text

Specialized Index Types: TTL, Partial, Unique & Text

Exam Focus: The MongoDB Certified Associate Developer Exam tests the exact configuration parameters, mechanics, and limitations of specialized index types: TTL index date requirements and 60-second background thread purge cycles, Partial Index partialFilterExpression query matching rules, Unique Index null-handling and Partial Unique patterns, Text Index single-index limits and $text/textScore syntax, and 2dsphere GeoJSON coordinate ordering ([longitude, latitude]).

---

1. Time-To-Live (TTL) Indexes

A TTL (Time-To-Live) Index is a specialized single-field index that automatically purges documents from a collection after a specified amount of time. TTL indexes are ideal for transient operational data such as user authentication sessions, temporary verification tokens, machine telemetry caches, and ephemeral event logs.

Syntax & Configuration

// Expire documents 3600 seconds (1 hour) after the 'created_at' timestamp
db.user_sessions.createIndex(
  { created_at: 1 },
  { expireAfterSeconds: 3600 }
);

Fixed-Interval Expiration vs. Dynamic Custom Expiration

• Fixed-Interval Expiration: As shown above, every document expires when current_time >= created_at + expireAfterSeconds.
• Dynamic Custom Expiration (Per-Document TTL): Set expireAfterSeconds: 0 and store the explicit future expiration date in each document's Date field:

// Create dynamic TTL index
db.coupons.createIndex({ expires_at: 1 }, { expireAfterSeconds: 0 });

// Document with custom future expiration date
db.coupons.insertOne({
  code: "SPRING2026",
  expires_at: ISODate("2026-09-15T00:00:00Z") // Purged after this exact instant
});

Critical TTL Rules & Limitations (High-Yield Exam Points)

1. BSON Date Type Requirement: The indexed field must contain a BSON Date (ISODate) or an array of BSON Dates. If the field contains a string, timestamp, integer, or null, the document will never be expired.
2. Single-Field Only: TTL indexes cannot be compound indexes.
3. Forbidden on _id: You cannot build a TTL index on the primary key _id.
4. 60-Second Background Thread: Document deletion is managed by an internal background thread that executes once every 60 seconds. Therefore, document expiration is not instantaneous; a document may remain visible for up to 60 seconds after its expiration threshold.
5. Capped Collections Incompatibility: TTL indexes cannot be created on capped collections because capped collections prohibit individual document deletions.
6. Modifying TTL Thresholds: You can modify expireAfterSeconds on an existing index using the collMod command without dropping and rebuilding the index:

db.runCommand({
  collMod: "user_sessions",
  index: {
    keyPattern: { created_at: 1 },
    expireAfterSeconds: 7200 // Extended to 2 hours
  }
});

---

2. Partial Indexes

A Partial Index indexes only documents in a collection that satisfy a specified filter expression (partialFilterExpression). By omitting documents that are never queried, partial indexes reduce index size in RAM, minimize disk usage, and cut write maintenance overhead.

Syntax

// Index 'email' ONLY for documents where status is 'ACTIVE'
db.users.createIndex(
  { email: 1 },
  { partialFilterExpression: { status: "ACTIVE" } }
);

Query Utilization Rules for Partial Indexes

For MongoDB to use a partial index during query execution, the query filter must be a guaranteed subset of the partialFilterExpression:

// Uses the partial index (filter explicitly includes status: "ACTIVE"):
db.users.find({ email: "alex@example.com", status: "ACTIVE" });

// CANNOT use the partial index (filter does not guarantee status is "ACTIVE"):
db.users.find({ email: "alex@example.com" }); // Triggers COLLSCAN

Allowed Operators in partialFilterExpression

The partialFilterExpression supports: $eq, $exists: true, $gt, $gte, $lt, $lte, $type, and $and at the top level. Operators such as $expr, $where, $text, and $regex are not permitted.

---

3. Unique Indexes & The Partial Unique Pattern

A Unique Index guarantees that the indexed field does not store duplicate values across the collection, rejecting any write operation that violates uniqueness with an E11000 duplicate key error.

Syntax

db.accounts.createIndex({ username: 1 }, { unique: true });

The Missing/Null Field Problem in Unique Indexes

In MongoDB, if a document does not contain an indexed field, MongoDB stores null as the index entry. Consequently, in a standard unique index, you can insert at most ONE document that lacks the indexed field. A second insert with a missing or null field throws an E11000 duplicate key error on { field: null }!

The Solution: Partial Unique Indexes (Modern Best Practice)

To enforce uniqueness on a field when present while allowing multiple documents to omit the field, combine unique: true with a partialFilterExpression:

// Allow multiple users without phone numbers, but enforce uniqueness for all existing phones
db.users.createIndex(
  { phone_number: 1 },
  {
    unique: true,
    partialFilterExpression: { phone_number: { $type: "string" } }
  }
);

[!TIP] Partial Unique vs Sparse Indexes: While legacy MongoDB supported sparse: true indexes, Partial Unique indexes are strictly preferred because sparse indexes only check field existence ($exists), whereas partialFilterExpression allows fine-grained type and value constraints.

---

4. Text Indexes & Full-Text Search

MongoDB provides Text Indexes to support text search queries on string content, featuring natural language tokenization, stemming (e.g., matching "running" to "run"), and case-insensitive matching.

Key Rules & Syntax

// Create a text index on multiple string fields
db.articles.createIndex(
  { title: "text", body: "text" },
  { weights: { title: 10, body: 1 }, name: "idx_article_text_search" }
);

• Single Text Index Limit: A collection can have at most ONE text index. However, a single text index can compound multiple fields (compound text index) or index all string fields using wildcard syntax ({ "$**": "text" }).
• Executing Queries with $text and $search:

// Search for articles containing 'mongodb' AND 'performance', but NOT 'legacy'
db.articles.find({
  $text: { $search: "mongodb performance -legacy" }
});

• Relevance Sorting via textScore: MongoDB computes a numerical relevance score for each matching document. You can project and sort by this score using $meta: "textScore":

db.articles.find(
  { $text: { $search: "wiredtiger optimization" } },
  { score: { $meta: "textScore" } }
).sort({
  score: { $meta: "textScore" }
});

---

5. Geospatial 2dsphere Indexes

A 2dsphere Index supports queries that calculate geometries on an Earth-like sphere using WGS84 coordinates.

Coordinate Order: [ Longitude, Latitude ]

[!WARNING] Critical Exam Rule: GeoJSON coordinate arrays in MongoDB strictly follow the order: [ Longitude, Latitude ] (X before Y). Reversing the coordinates ([ Lat, Long ]) will store invalid coordinates or cause queries to fail.

// Document containing GeoJSON Point
db.places.insertOne({
  name: "Central Park",
  location: {
    type: "Point",
    coordinates: [ -73.9654, 40.7829 ] // [ Longitude, Latitude ]
  }
});

// Create 2dsphere index
db.places.createIndex({ location: "2dsphere" });

// Proximity search: Find places within 5,000 meters
db.places.find({
  location: {
    $near: {
      $geometry: { type: "Point", coordinates: [ -73.9654, 40.7829 ] },
      $maxDistance: 5000
    }
  }
});

Specialized Index Comparison Matrix