Querying: find, filters and projections

Now the day-to-day: reading documents. If you know SQL — and after nine modules you do — the mental translation is mostly mechanical, with a handful of genuine surprises that cause real bugs. This lesson is the translation and the surprises.

The shape of a query

db.books.find({ "author.city": "Pune" }, { title: 1, _id: 0 })
//        ^^^^^  ^^^^^^^^^^^^^^^^^^^^^^^  ^^^^^^^^^^^^^^^^^^^^
//     collection   filter (the WHERE)     projection (the SELECT columns)

Against SELECT title FROM books WHERE author_city = 'Pune', the two pieces map cleanly:

The filter is your WHERE — a document describing what to match.
The projection is your column list — which fields to return, 1 to include, 0 to exclude.

findOne returns the first match (like LIMIT 1); find returns a cursor you iterate.

The filter, translated from SQL

// WHERE title = 'The Long Monsoon'
db.books.find({ title: "The Long Monsoon" })

// WHERE price > 40000
db.books.find({ price: { $gt: 40000 } })

// WHERE price BETWEEN 30000 AND 50000
db.books.find({ price: { $gte: 30000, $lte: 50000 } })

// WHERE _id IN (1, 3)
db.books.find({ _id: { $in: [1, 3] } })

// WHERE copies = 1 OR price < 30000
db.books.find({ $or: [ { copies: 1 }, { price: { $lt: 30000 } } ] })

// WHERE author_city = 'Pune' AND copies > 1     -- AND is implicit: just list the fields
db.books.find({ "author.city": "Pune", copies: { $gt: 1 } })

The operators: $gt $gte $lt $lte $ne $in $nin for comparison; $and $or $not $nor for logic. AND is implicit — multiple fields in one filter document all have to match. $or must be written out.

Two things SQL does not have

Reaching into nested data with dot notation:

db.books.find({ "author.city": "Pune" })

"author.city" reaches inside the embedded author object. This is the document model paying off — no join to get there. It works to any depth, and into arrays of subdocuments.

Querying arrays directly:

// books tagged 'fiction' — matches if the array CONTAINS it
db.books.find({ categories: "fiction" })

// tagged both 'fiction' and 'literary'
db.books.find({ categories: { $all: ["fiction", "literary"] } })

// at least one phone recorded
db.members.find({ "phones.0": { $exists: true } })

{ categories: "fiction" } matching a document whose categories array contains "fiction" is the one to internalise: a scalar match against an array field means "contains". For conditions on array elements together, $elemMatch matches a single element against several criteria at once.

The surprises that cause real bugs

`null` matches missing fields

This is the big one, and it catches everyone coming from SQL. In the seeded data, one member has no email field at all:

db.members.find({ email: null }).count()      // → 1  (the member with NO email field)

{ email: null } matches documents where email is explicitly null AND documents where email does not exist. In SQL these are distinct; here they are conflated. To tell them apart:

db.members.find({ email: { $exists: false } })            // field truly absent
db.members.find({ email: { $eq: null, $exists: true } })  // field present and null

If you have ever written { status: null } expecting "explicitly null" and got every document that simply lacks the field, this is why.

There is no schema, so a field can be any type

db.books.find({ price: { $gt: 40000 } })

If some documents stored price as the string "45000", they are silently skipped — a string is not $gt a number, and no error is raised. The query returns fewer rows than it should and nothing tells you. This is the "schema in the application" problem from the first lesson, showing up at query time. Validate on write, or check $type when it matters.

Comparison across types has a fixed, surprising order

When types are mixed, MongoDB does not error — it uses a defined ordering (null < numbers < strings < objects < arrays < ...). So a range query over a field with mixed types returns something, and it is rarely what you meant. Another argument for validation.

`count()` versus `countDocuments()`

The old db.collection.count() can return a fast, cached, approximate number based on metadata. Use countDocuments(filter) for an accurate count, and estimatedDocumentCount() only when you knowingly want the fast estimate of the whole collection.

Projection, sorting, paging

db.books.find({}, { title: 1, price: 1, _id: 0 })      // include title & price, drop _id
db.books.find({}, { "author.name": 1 })                // project a nested field

db.books.find().sort({ price: -1 })                    // ORDER BY price DESC  (1 asc, -1 desc)
db.books.find().sort({ price: -1 }).skip(20).limit(10) // OFFSET 20 LIMIT 10

Two notes. _id is returned unless you exclude it — the one field you can mix into an otherwise-include projection. And skip is OFFSET, and it has the same problem OFFSET does — the database still walks the skipped documents, so deep paging is slow. For large collections, page by a filter on the last-seen _id ({ _id: { $gt: lastId } }) instead, which is the keyset-pagination idea from the SQL side.

Updating and deleting, briefly

db.books.updateOne({ _id: 1 }, { $set: { copies: 4 } })          // change fields
db.books.updateMany({ "author.city": "Pune" }, { $inc: { copies: 1 } })
db.books.updateOne({ _id: 1 }, { $push: { categories: "award-winning" } })  // array append
db.books.deleteOne({ _id: 4 })

You must use an update operator ($set, $inc, $push, $pull, ...). A bare updateOne({_id:1}, { copies: 4 }) does not set copies to 4 — modern drivers reject it, and the classic footgun in older ones was that it replaced the entire document with { copies: 4 }, destroying every other field. Always name the operator.

$inc is genuinely useful: incrementing a counter is atomic on a single document, no read needed.

The query-to-index link, previewed

Every filter you write is a candidate for an index, and the rules are the ones you already know from the SQL performance module — the leftmost-prefix rule for compound indexes applies almost unchanged. The next lesson but one is indexes; for now, know that db.books.find(...).explain() exists and reads much like EXPLAIN.

Check your work

The two parts of a find. The filter (the WHERE) and the projection (the column list).

How AND is expressed. Implicitly — multiple fields in one filter document.

How OR is expressed. Explicitly, with $or.

How to query nested data. Dot notation — "author.city" — no join needed.

What a scalar match against an array field means. Contains — { categories: "fiction" } matches any document whose array includes it.

The null surprise. { field: null } matches both explicit null and missing; distinguish with $exists.

Why a range query can silently drop rows. A field stored as the wrong type is skipped with no error.

Why countDocuments() over count(). count() can return a cached approximate number.

How to include a nested field but drop _id. { "author.name": 1, _id: 0 } — _id is returned unless excluded.

Why deep skip is slow. Like OFFSET, it walks the skipped documents; page by _id instead.

Why an update needs an operator. Without $set, the operation historically replaced the whole document; always name the operator.

Which update is atomic and needs no read. $inc on a single document.

Practice

Seed a small books collection and translate five of your SQL WHERE clauses into filters.
Write a query with two conditions (implicit AND), then rewrite one branch as $or.
Query a nested field with dot notation and confirm no join was needed.
Match an array field with a scalar and confirm it means "contains". Then use $all.
Insert one document missing a field and one with the field explicitly null. Query with { field: null } and count. Then separate them with $exists.
Store a numeric field as a string in one document and run a $gt query. Confirm it is silently skipped.
Compare count() and countDocuments() on the same filter.
Project a nested field while excluding _id.
Sort descending, then add skip and limit. Then rewrite the paging with an _id filter.
Update one document with $set and $inc. Then try a bare updateOne({_id:1},{x:1}) and observe what your driver does.
$push a value onto an array field and read it back.
Run .explain() on one of your queries and find the part that looks like EXPLAIN.

Official documentation

MongoDB — Query documents — Filters, the operators, and iterating a cursor.
MongoDB — Query operators — Every $gt, $in, $exists, $all, $elemMatch.
MongoDB — Query on embedded documents and arrays — Dot notation and array matching.
MongoDB — Query for null or missing fields — The null-versus-missing surprise, spelled out.
MongoDB — Update operators — $set, $inc, $push, $pull.
MongoDB — Comparison and type order — The fixed ordering across types.

Next: the aggregation pipeline — MongoDB's GROUP BY and much more.