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

A document or an array of documents. Field paths and value types are checked against it. It stays in your browser.

Operators: $eq $ne $gt $gte $lt $lte $in $nin $regex $exists $elemMatch. Lines are ANDed. $elemMatch takes sub-conditions joined by &&, like: orders $elemMatch total $gte 100 && status $eq paid.

Field and direction (1, -1, asc, desc). With a group stage these refer to the group's output fields.

Fields to keep, or -field to drop. Only _id can be dropped while others are kept.

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 })
Conditions checked against your sample
FieldOperatorValue as typedType in sample
status$eqactivestring
age$gte21number
address.city$inBengaluru, Mysurustring
  • 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?

Position of a filter field in an ESR compound index
OperatorCounts asIndex positionNote
$eq (or a plain value)EqualityFirstSeveral equality fields can be in any order among themselves
$in with up to 200 valuesEqualityFirstWith a sort, the server merges the sorted ranges (SORT_MERGE)
$in with 201 or more valuesRangeAfter the sortThe manual says the threshold may change between versions
$gt $gte $lt $lteRangeAfter the sortPut two bounds on one field as a single range
$ne $ninRangeAfter the sortThe manual lists them as range operators
$regexRangeAfter the sortThe manual lists it as a range operator
$existsTreated as rangeAfter the sortNot named on the ESR page; verify with explain()
$elemMatchEquality on an array pathFirstMultikey index; verify with explain()

How to build a MongoDB query from a sample document

  1. 1
    Paste 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.
  2. 2
    Write 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.
  3. 3
    Add 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.
  4. 4
    Copy 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?
Order a compound index as Equality fields, then Sort fields, then Range fields. Equality first keeps the remaining keys in sorted order, so a query that sorts can read documents already in order. MongoDB's manual uses { directors: 1, year: 1, runtime: 1 } for a query on directors and runtime that sorts by year.
When should I use ERS instead of ESR?
The manual says to use Equality, Range, Sort when the range predicate is very selective: fewer documents then reach the in-memory sort. If avoiding an in-memory sort matters more, stay with ESR. This page writes ESR and tells you when a range and a sort are both present so you can compare the two with explain().
Why is my $in treated as a range after 200 values?
With a sort, an $in of fewer than 201 values behaves like equality because the server merges the sorted results of each value (a SORT_MERGE stage). At 201 or more it behaves like a range predicate, and the fields after it can't be used for the sort. The manual adds that this number isn't guaranteed to stay the same for all versions.
Does Mongoose cast values inside an aggregate pipeline?
No. Mongoose casts filter values in find() using the schema, but its documentation says it does not cast pipeline stages. A $match on _id with a plain string will not match stored ObjectIds, so the Mongoose output here writes new Types.ObjectId(...) and new Date(...) explicitly.
Why does $elemMatch matter for arrays of objects?
Two separate conditions on an array's fields can be satisfied by different elements. $elemMatch requires one single element to satisfy all its conditions. The manual's example: { results: { $elemMatch: { product: "xyz", score: { $gte: 8 } } } } matches only when one result has both.
Can I mix included and excluded fields in a projection?
Not for ordinary fields: a projection either lists fields to keep or fields to drop. The exception is _id, which you can drop while keeping others, as in email, status, -_id. The page rejects any other mix with a message naming the fields.
Can this page run the query or measure the index?
No. It never connects to a database: the sample, the conditions and the generated code stay in the page. It can only suggest the index; use explain("executionStats") on your own cluster to see whether the plan uses it.

Sources

Related tools

See all 135 free tools

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