MongoDB query builder from a sample document
Paste one sample document, write conditions as field, operator and value, and get find() or aggregation code for mongosh, the Node driver, Mongoose or PyMongo. Field paths and value types are checked against your sample, and the index that serves the query is ordered by MongoDB's Equality, Sort, Range rule.
Updated
Index that serves this query
{ status: 1, "address.city": 1, created_at: -1, age: 1 }
2 equality, 1 sort, 1 range (ESR order)
mongosh find()
db.users.find(
{
status: "active",
age: { $gte: 21 },
"address.city": { $in: ["Bengaluru", "Mysuru"] },
},
{ email: 1, status: 1, _id: 0 }
).sort({ created_at: -1 }).limit(20)
// Index that serves it (ESR order)
db.users.createIndex({ status: 1, "address.city": 1, created_at: -1, age: 1 })| Field | Operator | Value as typed | Type in sample |
|---|---|---|---|
| status | $eq | active | string |
| age | $gte | 21 | number |
| address.city | $in | Bengaluru, Mysuru | string |
- Conditions on different lines are ANDed. For $or, wrap two filters by hand: { $or: [ {...}, {...} ] }.
- A lone $eq is written in its short form (status: "active"), which MongoDB treats the same as { $eq: "active" }.
- The index is Equality, then Sort, then Range. If your range condition is very selective, the manual says to try Equality, Range, Sort instead (the sort then happens in memory on fewer documents); compare both with explain().
- An index suggestion cannot be verified from a sample: run explain("executionStats") on the real collection and check for IXSCAN and no SORT stage.
How is the query and its index worked out from a sample document?
The sample document is read as JSON and every field path in it is recorded, including paths inside embedded objects and arrays of objects (address.city, orders.total). Each filter line names a path, an operator and a value. The path must exist in the sample, and the value is typed from the field: a number field needs a number, a 24-hex string field becomes an ObjectId, an ISO date string becomes a Date, and a boolean field needs true or false. Mistyped paths get the closest real paths back.
find() mode writes the filter, then the projection, sort and limit. Pipeline mode writes $match first (so it can use an index), then $lookup, $group, $sort, $project and $limit, in that order. After a $group the documents only carry the group's output fields, so the sort and projection are checked against those instead of the sample.
The index follows MongoDB's ESR guideline: fields with equality conditions first, then the sort fields in the sort's own direction, then fields with range conditions. $ne, $nin and $regex count as range operators; $in counts as equality up to 200 values and as a range from 201. Where one field is both filtered and sorted, it sits in the sort position once. When $lookup or $group comes before $sort, the sort can no longer use the index, so only the $match fields are indexed.
index keys = [ equality fields ] + [ sort fields ] + [ range fields ]
find({ status: 'active', age: { $gte: 21 } }).sort({ created_at: -1 })
-> { status: 1, created_at: -1, age: 1 }Which operator goes in which part of the index?
| Operator | Counts as | Index position | Note |
|---|---|---|---|
| $eq (or a plain value) | Equality | First | Several equality fields can be in any order among themselves |
| $in with up to 200 values | Equality | First | With a sort, the server merges the sorted ranges (SORT_MERGE) |
| $in with 201 or more values | Range | After the sort | The manual says the threshold may change between versions |
| $gt $gte $lt $lte | Range | After the sort | Put two bounds on one field as a single range |
| $ne $nin | Range | After the sort | The manual lists them as range operators |
| $regex | Range | After the sort | The manual lists it as a range operator |
| $exists | Treated as range | After the sort | Not named on the ESR page; verify with explain() |
| $elemMatch | Equality on an array path | First | Multikey index; verify with explain() |
How to build a MongoDB query from a sample document
- 1Paste one real documentCopy a document from Compass or mongosh (extended JSON with $oid and $date also works). Paste an array of a few if some fields are missing in some documents.
- 2Write the filter linesOne condition per line as field operator value, for example age $gte 21. Dotted paths reach into embedded objects. Errors name the line and the real paths nearby.
- 3Add sort, projection and limitSort as created_at -1. Projection as a list of fields to keep, or -field to drop. For a pipeline, switch the query type and add a group or a lookup.
- 4Copy the code and the indexChoose mongosh, Node, Mongoose or PyMongo. The createIndex line below the query is in ESR order; create it once, then check the plan with explain().
Frequently asked questions
What is the ESR rule for MongoDB indexes?
When should I use ERS instead of ESR?
Why is my $in treated as a range after 200 values?
Does Mongoose cast values inside an aggregate pipeline?
Why does $elemMatch matter for arrays of objects?
Can I mix included and excluded fields in a projection?
Can this page run the query or measure the index?
Sources
- MongoDB manual: Equality, Sort, Range guideline (key order, ERS, $in at 201 values, $ne/$nin/$regex as range) (checked 11 Nov 2026)
- MongoDB manual: $elemMatch (one element must satisfy all conditions) (checked 11 Nov 2026)
- MongoDB manual: $lookup (from, localField, foreignField, as; output is always an array) (checked 11 Nov 2026)
- MongoDB manual: Project fields to return from a query (inclusion and exclusion rules) (checked 11 Nov 2026)
- Node.js driver: retrieve data (find with sort, project, limit; aggregate) (checked 11 Nov 2026)
- PyMongo: specify a query (find, projection, sort, limit) (checked 11 Nov 2026)
- Mongoose API: Aggregate (does not cast pipeline stages) (checked 11 Nov 2026)
Need a custom dev tool for your team?
We build internal tools, dashboards, APIs, and SaaS platforms. If you can describe it, we can ship it.
Built by CountryDevs · No signup · No ads · We count visits, never what you enter