Published / updated29 Aug 2026

Querying and Filtering

Before you start

You need: CRUD (Article 02).

Time: about 45 minutes, plus the practice.

Learning objective

Write precise queries with the operator set, and control exactly which fields and how many documents come back.

Topics

• Comparison operators
• Logical operators
• Element and type operators
• Pattern matching
• Projection
• Sorting
• Paging with skip and limit
• Cursors
• Diagnosing empty results

Comparison operators

db.students.find({ marks: { $gt: 80 } })
db.students.find({ marks: { $gte: 80, $lte: 90 } })
db.students.find({ className: { $ne: "10th" } })
db.students.find({ className: { $in: ["9th", "10th", "11th"] } })
db.students.find({ className: { $nin: ["12th"] } })

Operator	Matches
$eq	Equal — implied by a bare value
$ne	Not equal
$gt / $gte	Greater than / or equal
$lt / $lte	Less than / or equal
$in	Any value in an array
$nin	None of them

Several operators on one field combine as AND:

db.students.find({ marks: { $gte: 35, $lt: 90 } })

$ne and $nin also match documents where the field is absent. A student with no className field at all is returned by { className: { $ne: "10th" } }, which is usually not what was meant:

db.students.find({ className: { $exists: true, $ne: "10th" } })

Dates

db.payments.find({
    paidOn: { $gte: ISODate("2024-06-01"), $lt: ISODate("2024-07-01") }
})

Half-open ranges, exactly as in SQL. $lte: ISODate("2024-06-30") compares against midnight, so a payment at 10 a.m. on the 30th is excluded.

A date stored as a string compares lexically, not chronologically — which is why the previous article insisted on ISODate.

Logical operators

// AND — implicit between different fields
db.students.find({ schoolId: 1, className: "10th", status: "Active" })

// AND — explicit, needed for two conditions on the same field name
db.students.find({
    $and: [
        { marks: { $gt: 35 } },
        { marks: { $lt: 90 } }
    ]
})

db.students.find({
    $or: [
        { className: "10th" },
        { marks: { $gte: 90 } }
    ]
})

db.students.find({
    $nor: [{ status: "Inactive" }, { status: "Graduated" }]
})

db.students.find({ marks: { $not: { $gt: 90 } } })

Combining $or with other conditions requires care:

// Wrong — the second schoolId key silently replaces the first
db.students.find({
    $or: [{ className: "10th" }, { className: "11th" }],
    schoolId: 1
})

That one is actually correct, because the keys differ. This is the broken form:

// Wrong — duplicate keys in a JavaScript object; the last wins
db.students.find({ marks: { $gt: 35 }, marks: { $lt: 90 } })

The object literal has one marks key by the time MongoDB sees it, so only $lt: 90 applies. Use $and when the same field needs two separate condition objects, or combine them into one:

db.students.find({ marks: { $gt: 35, $lt: 90 } })

$or with a tenant filter must scope every branch:

db.students.find({
    schoolId: 1,
    $or: [
        { name: { $regex: "Ravi", $options: "i" } },
        { rollNumber: { $regex: "0012" } }
    ]
})

schoolId sits outside the $or, so it applies to both branches. Putting it inside one branch only is a tenant leak.

Element and type operators

db.students.find({ scholarship: { $exists: true } })
db.students.find({ address: { $exists: false } })
db.students.find({ rollNumber: { $type: "string" } })
db.students.find({ rollNumber: { $type: "number" } })

$type is how you find inconsistent data, which flexible schema makes possible:

db.students.find({ marks: { $not: { $type: "number" } } })

That returns every document where marks is a string, missing, or anything else — the query to run before trusting a numeric aggregate.

null versus missing

db.students.find({ address: null })                          // null OR absent
db.students.find({ address: { $eq: null, $exists: true } })  // explicitly null
db.students.find({ address: { $exists: false } })            // absent only

{ field: null } matches both. This surprises everyone once and matters when "not recorded" and "recorded as nothing" are different states.

Pattern matching

db.students.find({ name: { $regex: "^Ravi" } })                    // starts with
db.students.find({ name: { $regex: "Kumar$" } })                   // ends with
db.students.find({ name: { $regex: "Kumar" } })                    // contains
db.students.find({ name: { $regex: "ravi", $options: "i" } })      // case-insensitive
db.students.find({ name: /^Ravi/i })                                // literal syntax

An anchored pattern can use an index; an unanchored one cannot.

db.students.find({ name: /^Ravi/ })      // index seek possible
db.students.find({ name: /Ravi/ })       // full collection scan

The same rule as LIKE 'x%' versus LIKE '%x%' in SQL. On a large collection that is the difference between instant and seconds.

Case-insensitive regex defeats a standard index entirely, even when anchored. For case-insensitive search either store a normalised lowercase field and index that, or use a text index:

db.students.createIndex({ name: "text" })
db.students.find({ $text: { $search: "Ravi Kumar" } })

A text index tokenises words and supports relevance scoring. It does not do partial-word matching, so it suits search boxes and not autocomplete.

Escape user input before building a regex. A user searching for .* otherwise matches everything, and a crafted pattern can cause catastrophic backtracking:

const escaped = term.replace(/[.*+?^${}()|[\]\\]/g, "\\$&");
db.students.find({ name: { $regex: escaped, $options: "i" } })

Projection

db.students.find({ className: "10th" }, { name: 1, rollNumber: 1 })
db.students.find({ className: "10th" }, { name: 1, rollNumber: 1, _id: 0 })
db.students.find({ className: "10th" }, { parentPhone: 0, address: 0 })

1 includes, 0 excludes. _id is included unless you exclude it explicitly.

Inclusion and exclusion cannot be mixed, except for _id:

db.students.find({}, { name: 1, address: 0 })     // error
db.students.find({}, { name: 1, _id: 0 })          // allowed

Two reasons projection matters:

Less data over the wire. A list page needing four fields should not fetch a document with an embedded array of forty subjects.

Accidental disclosure. Returning whole documents means a passwordHash field added later reaches every client automatically — the same problem as SELECT *.

db.students.find(
    { className: "10th" },
    { name: 1, rollNumber: 1, "address.city": 1, _id: 0 }
)

Nested fields project with dot notation.

Sorting

db.students.find().sort({ name: 1 })                       // ascending
db.students.find().sort({ marks: -1 })                     // descending
db.students.find().sort({ className: 1, section: 1, name: 1 })

Without sort, document order is undefined. It often looks like insertion order and changes when documents are updated, moved or an index is added.

MongoDB sorts mixed BSON types by a fixed type order — null before numbers before strings — so a field with inconsistent types produces an order nobody expects.

A large sort without an index fails. MongoDB caps in-memory sorts at 100 MB:

Executor error: Sort exceeded memory limit of 104857600 bytes

The fix is an index on the sort fields, not allowDiskUse — that makes it slow rather than failing.

Paging with skip and limit

db.students.find()
           .sort({ className: 1, section: 1, name: 1, _id: 1 })
           .skip(40)
           .limit(20)

skip((page - 1) * pageSize) then limit(pageSize).

The _id tiebreaker is not optional. With several students sharing a class, section and name, their relative order is undefined between queries — so one appears on page 2 and page 3 while another is never shown. Ending the sort on a unique field fixes it.

skip gets slower as the offset grows. MongoDB walks and discards every skipped document, so page 500 reads 10,000 documents to return 20.

For deep paging, use a range on the last seen value:

// First page
db.students.find({ schoolId: 1 }).sort({ _id: 1 }).limit(20)

// Next page — from the last _id of the previous page
db.students.find({ schoolId: 1, _id: { $gt: lastSeenId } }).sort({ _id: 1 }).limit(20)

This is cursor paging. It stays fast at any depth and cannot skip or repeat a document when the collection changes between pages — which offset paging can.

The cost is no random access to page 500. For an infinite-scroll list that is no cost at all.

const total = db.students.countDocuments({ schoolId: 1, className: "10th" })
const items = db.students.find({ schoolId: 1, className: "10th" })
                         .sort({ name: 1, _id: 1 })
                         .skip(40)
                         .limit(20)
                         .toArray()

A total count needs a separate query — there is no single-round-trip equivalent of SQL's COUNT(*) OVER().

Cursors

const cursor = db.students.find({ schoolId: 1 })

cursor.hasNext()
cursor.next()
cursor.toArray()
cursor.forEach(s => print(s.name))

db.students.find().limit(5).toArray()

find returns a cursor, not documents. Nothing is fetched until you iterate — the query is lazy, exactly like IQueryable.

toArray() loads every result into memory. On a large collection, iterate instead:

db.students.find({ schoolId: 1 }).forEach(student => process(student))

A cursor times out after 10 minutes of inactivity by default. Long processing loops need noCursorTimeout or, better, batching by _id range.

Explaining a query

db.students.find({ schoolId: 1, className: "10th" }).explain("executionStats")

Three fields tell you what you need:

Field	Meaning
stage	COLLSCAN (scan) or IXSCAN (index)
totalDocsExamined	Documents read
nReturned	Documents returned

COLLSCAN means no index was used. On a large collection that is the answer to "why is this slow".

totalDocsExamined far above nReturned means the index is not selective enough. Reading 50,000 documents to return 20 is a partial index at best.

The ideal is totalDocsExamined === nReturned, meaning the index found exactly the matching documents.

Diagnosing empty results

The most common MongoDB question, in order of likelihood.

1. Type mismatch.

db.students.findOne()                    // look at what is actually stored
db.students.find({ marks: { $type: "string" } })

{ marks: 87 } does not match { marks: "87" }. No coercion, no error.

2. _id as a string.

db.students.find({ _id: ObjectId("6650a1b2c3d4e5f6a7b8c9d0") })

3. Wrong collection or database.

db
show collections

A typo creates an empty collection silently.

4. Case sensitivity. "10th" and "10TH" are different values, and field names are case-sensitive too — className and classname are different fields.

5. A field that is absent, not null. { address: { $exists: false } } versus { address: null }.

6. Duplicate keys in the filter object.

db.students.find({ marks: { $gt: 35 }, marks: { $lt: 90 } })   // only $lt applies

7. Whitespace. A value imported from CSV with a trailing space does not match a trimmed filter. db.students.findOne({ rollNumber: /^NCA-2024-0012\s*$/ }) confirms it.

Compass's Schema tab samples the collection and reports every field, its types and how often each appears. That is the fastest way to find the inconsistency — it shows at a glance that marks is a number in 94% of documents and a string in 6%.

Errors you will hit

What you see	Cause	Fix
A query matches documents missing the field	null matches missing as well as null	Use $exists
String comparison misses obvious matches	Case-sensitive by default	Use a regex, or a collation
$or returns too much	Mixed with other conditions at the top level	Nest it explicitly
Sorting is slow on a big collection	No index on the sort field	Add one
Projection excludes _id unexpectedly	_id is included unless excluded	{ _id: 0 }

{ section: null } matches documents where section is missing entirely. That is rarely what you meant — $exists distinguishes them.

Common mistakes

• Comparing a number to a stored string
• Querying _id with a string
• $ne and $nin unexpectedly matching missing fields
• { field: null } matching absent documents too
• Duplicate keys in a filter object
• $or with the tenant filter inside one branch
• Unanchored or case-insensitive regex on a large collection
• Unescaped user input in a regex
• BETWEEN-style date ranges dropping the last day
• Dates stored as strings
• No sort, then relying on the order
• skip/limit with a non-unique sort, repeating or skipping rows
• Deep skip paging instead of a range
• Mixing inclusion and exclusion in a projection
• Returning whole documents instead of projecting
• toArray() on a large result
• Never running explain

Practice

The course exercise is write filtered CRUD queries.

1. Seed thirty students across three classes with varied marks.
2. Find students with marks between 35 and 90, first with two conditions on one field in one object, then with duplicate keys. Compare the counts.
3. Query { className: { $ne: "10th" } } on data where one document has no className. Confirm it is returned, then exclude it with $exists.
4. Insert one marks as a string. Run a $gt query and confirm it is missed. Find it with $type.
5. Query { address: null } on data with both null and absent addresses. Distinguish them.
6. Search names with /^Ravi/ and /Ravi/. Run explain on both and compare stage and totalDocsExamined.
7. Add a case-insensitive option to the anchored query and re-run explain.
8. Pass .* as a user search term. Confirm it matches everything, then escape it.
9. Project four fields with _id: 0. Then mix inclusion and exclusion and record the error.
10. Add a passwordHash field to a document and run a query with no projection. Confirm it is returned.
11. Sort by class, section and name only. Page through data containing duplicate names and find a document appearing twice or never.
12. Add _id as the final sort field and confirm the paging is stable.
13. Time skip(0).limit(20) against skip(10000).limit(20) on 20,000 documents.
14. Implement cursor paging with _id: { $gt: lastSeen } and time page 500 against the skip version.
15. Filter a date range with $lte on the last day, then with $lt on the next day. Compare the counts.
16. Sort 200,000 documents on an unindexed field and record the memory-limit error.

Exercises 4, 11 and 13 correspond to a silent miss, a paging defect and a performance cliff.

You can now

• Write precise queries with comparison and logical operators
• Control returned fields with projection
• Tell a missing field from a null one
• Sort, skip and limit results
• Say why a query matched more than expected

Review questions

1. Why does { className: { $ne: "10th" } } return documents with no className?
2. Why do duplicate keys in a filter object silently lose a condition?
3. Why must a paged sort end on a unique field?
4. What do COLLSCAN and a high totalDocsExamined each tell you?

Next: Arrays and embedded documents