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.
Quick reference — reach for these first
db.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}).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.db.coll.find({ status: "A" }).sort({ createdAt: -1 }).explain("executionStats")Read winningPlan.stage (IXSCAN good, COLLSCAN bad) and totalDocsExamined vs nReturned.db.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.db.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
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
| Task | Command | What to check |
|---|---|---|
| Insert one document | db.users.insertOne({ name: "Al", age: 30, status: "A", tags: ["x"] }) | Returns { acknowledged: true, insertedId: ... }; db.users.countDocuments() increases by 1. |
| Query with comparison + membership operators | db.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 need | db.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 $set | db.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 index | db.users.createIndex({ email: 1 }, { unique: true }) | Appears in db.users.getIndexes(); duplicate email inserts now raise E11000. |
| Create a compound index in ESR order | db.orders.createIndex({ status: 1, createdAt: -1, amount: 1 }) | getIndexes() lists it; a query filtering status + sorting createdAt uses IXSCAN. |
| Read the query plan | db.orders.find({ status: "paid" }).sort({ createdAt: -1 }).explain("executionStats") | winningPlan.inputStage.stage is IXSCAN and totalDocsExamined is close to nReturned. |
| Group and aggregate totals | db.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 $lookup | db.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 $project | db.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 it | db.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
db.orders.find({ status: "paid" }).sort({ createdAt: -1 }).limit(20)
# Returns results but takes ~900ms on a 2M-doc collectiondb.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 stagedb.orders.createIndex({ status: 1, createdAt: -1 })
# Index built; appears in db.orders.getIndexes()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// compare executionTimeMillis before vs after
# executionTimeMillis ~3ms; ratio totalDocsExamined/nReturned = 1 (was 100000)Troubleshooting — symptom → first check → next check
| Symptom | First check | Establishes | Next 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
- Query Documents — db.collection.find & operatorsmongodb.com
- The ESR (Equality, Sort, Range) Rule for compound indexesmongodb.com
- Interpret Explain Plan Results (COLLSCAN vs IXSCAN)mongodb.com
- Aggregation Pipeline ($match/$group/$sort/$lookup/$project)mongodb.com
- Read Preference (replica-set read routing & staleness)mongodb.com
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.