← All cheatsheets

Databases

MongoDB — queries, indexes, and aggregation

Fast-lookup reference for MongoDB CRUD with query operators, compound indexes and the ESR rule, explain() plan reading (COLLSCAN vs IXSCAN), the aggregation pipeline, and when a document store fits versus SQL.

Docs-checkedmongodbnosqldocumentsindexesaggregation

Quick reference — reach for these first

find + operatorsdb.coll.find({ age: { $gt: 21 }, status: { $in: ["A","B"] }, name: { $regex: /^al/i } })Query with $gt/$gte/$lt, $in/$nin, $regex, $exists. Returns a cursor; add .limit(N).sort({f:1}).
createIndex (compound, ESR)db.coll.createIndex({ status: 1, createdAt: -1, age: 1 })Equality field first, then Sort field, then Range field. 1 = asc, -1 = desc. On 4.2+ a single optimized build briefly takes an exclusive lock at the start/end and yields in between; the old background option is ignored.
explain executionStatsdb.coll.find({ status: "A" }).sort({ createdAt: -1 }).explain("executionStats")Read winningPlan.stage (IXSCAN good, COLLSCAN bad) and totalDocsExamined vs nReturned.
aggregate pipelinedb.orders.aggregate([ { $match: { status: "paid" } }, { $group: { _id: "$userId", total: { $sum: "$amount" } } }, { $sort: { total: -1 } } ])Stages run in order and stream docs. Put $match (and $sort it can use) early so an index filters before $group.
updateOne $setdb.coll.updateOne({ _id: id }, { $set: { status: "B", updatedAt: new Date() } })Updates the FIRST match only. $set adds/overwrites fields; use $inc, $push, $unset for other edits. Add { upsert: true } to insert if absent.

Query optimization loop: diagnose, index (ESR), verify

MongoDB query optimization loopA slow query flagged COLLSCAN is diagnosed with explain executionStats, fixed by an ESR-ordered compound index, then re-checked and confirmed as IXSCAN with far fewer documents examined.1. Slow querystage: COLLSCANfull scan, in-memSORTexamined >> returned2. Diagnoseexplain("executionStats")read filter + sort+ docsExamined3. Index (ESR)createIndexE quality firstS ort nextR ange last4. Verifystage: IXSCANno SORT stageexamined ≈returned → fastre-run explain after indexing; compare executionTimeMillis before vs after

Text equivalent: Left to right flow. Step 1: a slow find() whose explain stage is COLLSCAN with docs examined far greater than returned. Step 2: run explain with executionStats to read the plan, filter, and sort. Step 3 (highlighted): createIndex in ESR order — Equality field, then Sort field, then Range field. Step 4: re-run explain and confirm the stage is now IXSCAN with docs examined close to docs returned, so the query is fast.

Common tasks

Task · Command · What to check

TaskCommandWhat to check
Insert one documentdb.users.insertOne({ name: "Al", age: 30, status: "A", tags: ["x"] })Returns { acknowledged: true, insertedId: ... }; db.users.countDocuments() increases by 1.
Query with comparison + membership operatorsdb.users.find({ age: { $gte: 18, $lt: 65 }, status: { $in: ["A","B"] } })Only docs in the age range with a matching status come back; counts match a manual filter.
Project just the fields you needdb.users.find({ status: "A" }, { name: 1, age: 1, _id: 0 })Each returned doc has only name and age (no _id), shrinking payload.
Update a single document with $setdb.users.updateOne({ name: "Al" }, { $set: { status: "B" } })Result shows matchedCount:1, modifiedCount:1; re-find confirms status is B.
Upsert (update or insert)db.users.updateOne({ email: "a@b.co" }, { $set: { active: true } }, { upsert: true })If absent, result has an upsertedId; the doc now exists with active:true.
Delete a single document (scoped filter)db.users.deleteOne({ _id: id })deletedCount:1 and the _id no longer returns from find.
Create a single-field indexdb.users.createIndex({ email: 1 }, { unique: true })Appears in db.users.getIndexes(); duplicate email inserts now raise E11000.
Create a compound index in ESR orderdb.orders.createIndex({ status: 1, createdAt: -1, amount: 1 })getIndexes() lists it; a query filtering status + sorting createdAt uses IXSCAN.
Read the query plandb.orders.find({ status: "paid" }).sort({ createdAt: -1 }).explain("executionStats")winningPlan.inputStage.stage is IXSCAN and totalDocsExamined is close to nReturned.
Group and aggregate totalsdb.orders.aggregate([{ $match: { status: "paid" } }, { $group: { _id: "$userId", total: { $sum: "$amount" }, n: { $sum: 1 } } }, { $sort: { total: -1 } }])One row per userId with summed amount; totals reconcile against a manual sum.
Join another collection with $lookupdb.orders.aggregate([{ $lookup: { from: "users", localField: "userId", foreignField: "_id", as: "user" } }, { $unwind: "$user" }])Each order gains a populated user object; ensure users._id (foreign key) is indexed.
Shape output with $projectdb.orders.aggregate([{ $project: { _id: 0, userId: 1, amount: 1, day: { $dateToString: { format: "%Y-%m-%d", date: "$createdAt" } } } }])Output docs contain only userId, amount, and a derived day string.
Scope a bulk write before running itdb.orders.updateMany({ status: "pending", createdAt: { $lt: cutoff } }, { $set: { status: "expired" } })[PROD-CHANGING] matchedCount equals the pre-count of that exact filter — never run updateMany/deleteMany with an empty {} filter.

Slow query: COLLSCAN → ESR-ordered compound index → IXSCAN, verified

index-tuning

1
Reproduce the slow query — recent paid orders for a status, newest first.
db.orders.find({ status: "paid" }).sort({ createdAt: -1 }).limit(20)
# Returns results but takes ~900ms on a 2M-doc collection
2
Diagnose with the real execution stats, not just the planner.
db.orders.find({ status: "paid" }).sort({ createdAt: -1 }).limit(20).explain("executionStats")
# winningPlan.stage: COLLSCAN
# executionStats: nReturned 20, totalDocsExamined 2000000, executionTimeMillis 912
# plus an in-memory SORT stage
3
Create a compound index in ESR order: Equality (status), then Sort (createdAt desc). No range field here.
db.orders.createIndex({ status: 1, createdAt: -1 })
# Index built; appears in db.orders.getIndexes()
4
Re-run explain to confirm the plan switched and work dropped.
db.orders.find({ status: "paid" }).sort({ createdAt: -1 }).limit(20).explain("executionStats")
# winningPlan.inputStage.stage: IXSCAN (no separate SORT stage)
# totalKeysExamined 20, totalDocsExamined 20, nReturned 20
5
Verify the win: docs examined now ≈ docs returned, and latency fell from ~900ms to single-digit ms.
// compare executionTimeMillis before vs after
# executionTimeMillis ~3ms; ratio totalDocsExamined/nReturned = 1 (was 100000)

Troubleshooting — symptom → first check → next check

SymptomFirst checkEstablishesNext check
A find() or aggregate is slow and CPU/IO spikes under load.db.coll.find(<filter>).explain("executionStats")winningPlan shows stage COLLSCAN and totalDocsExamined >> nReturned — no usable index, full collection scan.Create a compound index in ESR order (Equality, Sort, Range) matching the filter + sort, then re-run explain to confirm IXSCAN.
updateMany/deleteMany ran broader than intended and changed too many rows.Before running, db.coll.countDocuments(<filter>) and confirm the filter is non-empty.An empty {} filter matches the whole collection; the count reveals the true blast radius.[DESTRUCTIVE/PROD-CHANGING] Always scope with a selective filter; test on a copy or with a transaction/session; restore from backup if already run.
Insert/update fails with 'document is larger than the maximum size 16777216 bytes'.Object.bsonsize(doc) or review arrays/embedded data that grow unbounded.The BSON document exceeded the hard 16MB per-document limit (often an ever-growing embedded array).Remodel: move the unbounded data to its own collection referenced by _id, or store large blobs in GridFS; cap embedded arrays.
An aggregation with $lookup is slow as the joined collection grows.Run .explain() on the pipeline and check the $lookup sub-plan for a COLLSCAN on the foreign collection.$lookup scans the 'from' collection for every input doc when foreignField is unindexed.createIndex on the foreignField of the 'from' collection; put a selective $match before the $lookup to shrink the driving set.
Reads from a replica set return stale or missing just-written data.Check the connection/read preference: db.getMongo().getReadPref() or the URI readPreference=.Reads are going to a secondary (secondary / secondaryPreferred / nearest), which lags the primary by replication delay.Use readPreference=primary (or readConcern 'majority') for read-after-write consistency; reserve secondary reads for analytics/tolerant paths.
Query is fast in a test but slow in prod with the same index present.explain("executionStats") in prod and compare rejectedPlans plus totalKeysExamined vs totalDocsExamined.The planner picked a different (or less selective) index, or field order breaks ESR so it index-scans then re-sorts in memory.Reorder the compound index to ESR, drop redundant overlapping indexes, or use .hint() to pin the intended index; re-measure executionTimeMillis.

References

Commands use mongosh (the modern MongoDB shell) syntax; collection/field/placeholder names are in CAPS or illustrative. ESR, explain output fields, the 16MB BSON limit, and read-preference behavior are per the current MongoDB 7.x/8.x manual.