Queries, indexes and aggregation

A MongoDB query is a document that describes the documents you want, an index is a B-tree over the fields you filter and sort by — the same structure the SQL course's index lesson drew — and the aggregation pipeline is where the joins, groups and reshaping happen. The operators are different from SQL; the reasoning about what an index can and cannot do for a query is exactly the same, and explain is how you check rather than guess.

#Query operators

db.orders.find({ "customer.id": id, status: { $in: ["PAID", "SHIPPED"] }, placedAt: { $gte: since } })
         .sort({ placedAt: -1 }).limit(20)

Equality is a bare value; $in, $gt/$gte/$lt/$lte, $ne, $exists, $regex are the comparisons; $and is implicit between fields and explicit when you need the same field twice; $or is a list of clauses. Dot notation reaches into embedded documents and arrays — "lines.sku": "A-1" matches an order with any line for that SKU — and $elemMatch is for when several conditions must hold on the same array element (lines: { $elemMatch: { sku: "A-1", qty: { $gt: 5 } } }), because { "lines.sku": "A-1", "lines.qty": { $gt: 5 } } matches an order whose some line has the SKU and some other line has the quantity. Projection ({ lines: 0 }, or { orderNumber: 1, status: 1 }) trims the document the same way a select list does, and for the same reason: the network and the driver pay for every field you did not need. In Spring Data, Query and Criteria build the same documents, and a derived repository method builds them for you.

#Index types

A single-field index on placedAt serves a range and a sort. A compound index on { "customer.id": 1, placedAt: -1 } serves the query above entirely: equality on the first field, range and sort on the second, in the index's own order. A multikey index is what you get when an indexed field is an array — one index entry per element, so "lines.sku" indexes every SKU of every order, with the constraint that a compound index may contain at most one array field. A unique index enforces orderNumber; a partial index (partialFilterExpression: { status: "OPEN" }) indexes only the documents that match, which is the SQL course's partial index and the right answer for "open orders" when 99% are closed. A TTL index on a date field expires documents after N seconds, which is a session or a token store with no cleanup job. A text index does basic full-text search and is the point at which the search course starts making the case for Elasticsearch. And _id is always indexed and always unique.

Every index costs what it costs in SQL: write amplification on every insert and update of an indexed field, memory (the working set of indexes must fit in RAM or reads go to disk), and build time on a large collection — build in the background, or on a secondary first.

#The ESR rule: which fields, in which order

A compound index serves a query in one pass only when its fields are ordered Equality, Sort, Range. For { status: "PAID", placedAt: { $gte: since } } sorted by placedAt, the index is { status: 1, placedAt: -1 }: equality first narrows to one contiguous run of the index, and the sort field next means the run is already in sort order, so the range is a scan of a prefix of it. Put the range field before the sort field — { status: 1, total: 1, placedAt: -1 } for a query with a range on total — and the run is ordered by total, so the engine must fetch every matching document and sort in memory, which shows in the plan as a SORT stage and fails outright above 100 MB of results. The rule is the same one the SQL index lesson gave for column order; MongoDB has a name for it.

A query uses the index whose prefix matches its fields: { a: 1, b: 1, c: 1 } serves { a }, { a, b }, { a, b, c } and not { b } or { b, c }. One well-ordered compound index replaces three single-field ones and serves the sort too; three single-field indexes may be intersected by the planner, and usually are not.

#The aggregation pipeline

Anything past filter-project-sort is a pipeline: a list of stages, each transforming the stream of documents from the one before.

db.orders.aggregate([
  { $match: { status: "PAID", placedAt: { $gte: since } } },          // FIRST, so an index applies
  { $unwind: "$lines" },                                               // one document per line
  { $group: { _id: "$lines.sku", qty: { $sum: "$lines.qty" }, revenue: { $sum: { $multiply: ["$lines.qty", "$lines.unitPaise"] } } } },
  { $sort: { revenue: -1 } },
  { $limit: 10 },
  { $lookup: { from: "products", localField: "_id", foreignField: "sku", as: "product" } },   // a join, after the limit
  { $project: { sku: "$_id", qty: 1, revenue: 1, name: { $first: "$product.name" } } }
])

$match and $sort at the front can use an index; after a $group or $unwind nothing can, so the rule is to filter first and reduce the stream before the expensive stages. $group is GROUP BY with accumulators ($sum, $avg, $min, $max, $push, $addToSet, $first); $unwind turns an array into one document per element, which is what makes grouping over array contents possible and what multiplies the stream by the array's length. $lookup is a left outer join, and it is a join with a join's cost — put it after the $limit, never before a $group over the whole collection, and give the foreign collection an index on foreignField. $facet runs several sub-pipelines on one input for a page that needs counts and a list; $merge writes the result into a collection, which is a materialised view on a schedule. A pipeline that is too slow to run per request is the CQRS lesson's projection, maintained as documents change.

#Reading explain

db.orders.find({...}).sort({...}).explain("executionStats") is the plan. The lines that matter: the winning plan's stages read inside-out — IXSCAN on a named index feeding FETCH feeding SORT or LIMIT — and a COLLSCAN anywhere is a query with no usable index; totalKeysExamined against totalDocsExamined against nReturned, where a healthy query examines about as many keys as it returns and a bad one examines a million documents to return twenty; and a SORT stage, which means the index did not provide the order and the ESR rule was broken. The stage: "IXSCAN" block names indexBounds, so you can see that the range was applied on the index rather than after the fetch. The SQL course's "the index that was there and not used" lesson applies line for line: a query on "customer.id" does not use an index on "customerId", a $regex without an anchored prefix cannot use one, and $ne and $nin scan.