Unit 4: Indexing and Aggregation Framework

I. Orientation

MongoDB indexing and aggregation provide complementary mechanisms for intelligent data access: indexes reduce the work required to locate documents, while the aggregation framework transforms documents into analytical results through ordered processing stages.

• Document model: MongoDB stores BSON documents in collections; fields may contain scalar values, arrays, embedded documents, or references.
• Index principle: An index maintains selected field values in an ordered structure, commonly a B-tree, together with references to their documents.
• Query optimization: The query planner compares candidate execution plans and selects an efficient strategy using available indexes and query constraints.
• Aggregation principle: A pipeline passes documents through stages such as $match, $group, $project, and $sort, with each stage producing input for the next.
• Index trade-off: Indexes accelerate reads but consume storage and add work to inserts, updates, and deletions.
• Pipeline convention: Aggregation stages are written as an ordered array; changing stage order can change both results and performance.
• Execution goal: Efficient MongoDB workloads minimize documents examined, avoid unnecessary memory use, and return only required fields.

II. Index Structures — Organizing Efficient Access Paths

A. Single Indexes

A single-field index stores values from one document field and is most effective when queries repeatedly filter or sort by that field.

• Creation syntax: 1 requests ascending order and -1 requests descending order.

db.students.createIndex({ studentId: 1 })

• Equality lookup: With the index above, MongoDB can locate { studentId: 1042 } without scanning every document in students.
• Sorting support: An index { age: 1 } supports both ascending and reverse descending traversal for a query such as:

db.students.find({}).sort({ age: -1 })

• Unique constraint: A unique index rejects duplicate indexed values.

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

• Sparse behavior: { sparse: true } indexes only documents containing the field, whereas a normal single-field index also represents missing or null values according to MongoDB indexing rules.
• Partial index: partialFilterExpression indexes only documents satisfying a condition.

db.orders.createIndex(
  { customerId: 1 },
  { partialFilterExpression: { status: "OPEN" } }
)

• Array field: Indexing an array creates a multikey index with entries for array elements; MongoDB manages this automatically.
• Limitation: A field useful for one predicate may not help another. An index on department does not directly optimize a filter containing only salary.

B. Compound Indexes

A compound index stores two or more fields in a defined order, enabling efficient multi-condition filters and compatible sorts.

• Field order: The index { department: 1, salary: -1 } is first ordered by department, then by descending salary within each department.
• Prefix rule: The index supports queries using its leading prefixes:
  • { department: "Sales" }
  • { department: "Sales", salary: { $gt: 60000 } }
  • It is generally less useful for a query on salary alone because department is the leading field.
• ESR guideline: Compound indexes are commonly designed in Equality, Sort, Range order. For an equality filter on status, a sort on createdAt, and a range on total, a candidate is:

db.orders.createIndex({ status: 1, createdAt: -1, total: 1 })

• Sort compatibility: { category: 1, price: -1 } supports sorting by the same pattern or its complete reverse, subject to constraints on preceding fields.
• Selectivity: A field that eliminates many documents is selective, but selectivity must be balanced against prefix use and ESR ordering.
• Worked example: For:

db.products.find({
  category: "Laptop",
  price: { $lte: 1000 }
}).sort({ rating: -1 })

a candidate index is { category: 1, rating: -1, price: 1 }: category is equality, rating supplies sort order, and price is a range.

• Limitation: Redundant compound indexes increase storage and write cost; existing prefixes should be checked before creating another index.

C. Text Indexes

A text index supports language-aware search over string content by indexing terms rather than requiring exact whole-field equality.

• Creation syntax: Multiple fields can participate in one text index.

db.articles.createIndex({
  title: "text",
  body: "text"
})

• Search operator: $text searches fields included in the collection’s text index.

db.articles.find({
  $text: { $search: "database indexing" }
})

• Term behavior: An unquoted search commonly matches documents containing search terms, while an escaped quoted phrase requests phrase matching.

db.articles.find({
  $text: { $search: "\"aggregation pipeline\"" }
})

• Relevance score: The computed textScore can be projected and sorted.

db.articles.find(
  { $text: { $search: "NoSQL analytics" } },
  { score: { $meta: "textScore" }, title: 1 }
).sort({ score: { $meta: "textScore" } })

• Weights: Important fields can influence ranking more strongly.

db.articles.createIndex(
  { title: "text", body: "text" },
  { weights: { title: 5, body: 1 } }
)

• Language processing: Text indexes can apply stemming and stop-word rules based on the configured language.
• Limitation: A collection can have only one text index, although that index may cover several fields. Text search is less capable than specialized search systems for fuzzy matching, advanced ranking, and autocomplete.

III. Query Performance — Measuring Index Effectiveness

A. Performance with Indexing

Index performance depends on workload-specific measurements rather than the mere presence of an index.

• Collection scan: COLLSCAN examines collection documents directly; its cost tends to grow with collection size.
• Index scan: IXSCAN traverses index keys and then may fetch matching documents. A selective index can reduce examined documents from millions to a small result set.
• Selectivity ratio: A practical indicator is:

Selectivity = matching documents / total documents

Here, a smaller ratio usually indicates a more selective predicate. A query matching 100 of 1,000,000 documents has selectivity 0.0001.

• Write overhead: Every inserted or changed indexed value may require index maintenance. Ten indexes can make a write substantially more expensive than one index.
• Memory behavior: Frequently accessed index pages perform best when the working set fits available RAM; disk reads increase latency.
• Sort optimization: An index-compatible sort avoids an in-memory blocking sort and can reduce memory consumption.
• Low-cardinality fields: An index on a Boolean field such as active may be ineffective when almost every document has active: true.
• Operational discipline: Use getIndexes() to inspect definitions and $indexStats to observe index-access counts before removing apparently unused indexes.

B. Covered Queries and explain()

A covered query is answered entirely from index entries, while explain() reveals whether MongoDB actually used that access path efficiently.

• Coverage conditions: All filter and returned fields must be present in the same index, and the query must avoid conditions that require document inspection.
• Identifier exclusion: Because _id is returned by default, it must be excluded when _id is absent from the index.

db.users.createIndex({ email: 1, status: 1 })

db.users.find(
  { email: "a@example.com" },
  { _id: 0, email: 1, status: 1 }
)

• Coverage evidence: An execution plan containing IXSCAN without a document FETCH, together with totalDocsExamined: 0, indicates index-only retrieval.
• Execution inspection:

db.users.find({ status: "ACTIVE" })
  .explain("executionStats")

• Key metrics:
  • nReturned: Number of documents returned.
  • totalKeysExamined: Number of index entries inspected.
  • totalDocsExamined: Number of collection documents inspected.
  • executionTimeMillis: Observed execution time for that run.
• Plan interpretation: A strong selective plan often has examined counts close to nReturned; 50 returned documents with 500,000 documents examined signals poor filtering efficiency.
• Caution: Execution time varies with caching and system load, so stage structure and examination counts are often more reliable for diagnosis.

IV. Aggregation Framework — Transforming Documents into Results

A. Aggregation Pipeline Concepts

An aggregation pipeline is an ordered sequence of stages that filters, reshapes, combines, and summarizes documents.

• Stage flow: Each stage receives documents from the preceding stage and emits transformed documents.

db.orders.aggregate([
  { $match: { status: "PAID" } },
  { $group: {
      _id: "$customerId",
      totalSpent: { $sum: "$amount" }
  }},
  { $sort: { totalSpent: -1 } }
])

• $match: Filters documents; placing a selective $match early reduces downstream work and may permit index use.
• $project: Includes, excludes, renames, or computes fields, such as { total: { $multiply: ["$price", "$quantity"] } }.
• $group: Forms groups using _id as the grouping key and applies accumulators such as $sum, $avg, $min, $max, and $push.
• $unwind: Emits one document per array element. A document with items: ["A", "B"] becomes two pipeline documents.
• $sort, $skip, and $limit: Order and paginate results; deterministic pagination should sort by a stable, preferably unique key.
• $lookup: Performs a left outer join with another collection, while $facet runs multiple sub-pipelines over the same input.
• Optimization: Early filtering, index-supported sorting, reduced projections, and controlled array expansion limit CPU, memory, and intermediate-document volume.

B. Real-world Aggregation Examples

Real-world pipelines combine filtering, grouping, joining, and computed fields to produce operational or analytical outputs.

• Sales reporting: Monthly paid-order revenue can be calculated from order dates and amounts.

db.orders.aggregate([
  { $match: { status: "PAID" } },
  { $group: {
      _id: {
        year: { $year: "$orderDate" },
        month: { $month: "$orderDate" }
      },
      revenue: { $sum: "$amount" },
      orderCount: { $sum: 1 }
  }},
  { $sort: { "_id.year": 1, "_id.month": 1 } }
])

• Symbol definitions: _id.year and _id.month form the monthly group key; revenue is the sum of amount; orderCount counts grouped documents.
• Customer analysis: Grouping by $customerId can compute lifetime spending with $sum and average order value with $avg.
• Inventory monitoring: A pipeline can compute remaining = stock - reserved, then use $match to return products below a reorder threshold.
• Product popularity: $unwind: "$items" separates order lines, after which $group can total quantities by items.productId.
• Joined reporting: $lookup can enrich order documents with customer records, followed by $project to expose only customer name, order total, and date.
• Dashboard output: $facet can produce totals, status counts, and recent transactions in one request, with each facet returning its own result array.
• Practical constraint: Large grouping or sorting stages may require substantial memory; indexes, bounded date ranges, and allowDiskUse should be considered for production workloads.