Projection Sort and Paging

Inclusion and exclusion

The second argument to find is the projection. 1 includes a field, 0 excludes it, and you cannot mix the two in one projection. _id is the sole exception, so excluding it alongside inclusions is always allowed.

db.orders.find({}, { status: 1, total: 1 })            // plus _id
db.orders.find({}, { status: 1, total: 1, _id: 0 })    // exactly two fields
db.orders.find({}, { internal_notes: 0, audit: 0 })    // everything except these
db.orders.find({}, { status: 1, audit: 0 })            // Error, cannot mix

db.users.find({}, { "address.city": 1, _id: 0 })       // nested, dotted path
db.users.find({}, { "address.zip": 0 })                // drop one nested field

A projection that only names indexed fields plus _id: 0 can make the query covered, meaning the server answers from the index and never touches a document. Any exclusion projection rules that out.

Array projections

Three operators trim arrays on the server so you do not ship elements you will discard.

db.posts.find({}, { comments: { $slice: -5 } })          // 5 newest comments

// Positional: the array field MUST appear in the query
db.orders.find({ "items.sku": "A-1" }, { "items.$": 1 })

// $elemMatch does not need the query to mention it
db.orders.find({}, { items: { $elemMatch: { qty: { $gte: 10 } } } })

Both $ and $elemMatch return at most one element, and there is no projection operator that returns all matching elements. For that you need an aggregation with $filter.

Computed projections

Since 4.4 a find projection accepts aggregation expressions, which removes the most common reason to reach for an aggregation.

db.orders.find({}, {
  _id: 0,
  ref: 1,
  net: { $subtract: ["$total", "$tax"] },
  month: { $dateToString: { date: "$placed_at", format: "%Y-%m" } },
  big_items: { $filter: {
    input: "$items", as: "i", cond: { $gte: ["$$i.qty", 10] }
  } },
  label: { $cond: [ { $gt: ["$total", 500] }, "vip", "standard" ] },
  tax: { $cond: [ { $gt: ["$tax", 0] }, "$tax", "$$REMOVE" ] }
})

$$REMOVE drops the field for that document only, which is how you build a conditional projection without breaking the no-mixing rule. A computed field counts as an inclusion, so it cannot appear beside a 0.

Sorting and collation

1 ascending, -1 descending, applied left to right. A compound sort is only served by an index whose keys match in order and in direction, or in exactly inverted direction, because a B-tree can be walked backwards.

db.orders.find().sort({ placed_at: -1 })
db.orders.find().sort({ status: 1, placed_at: -1, _id: -1 })

// Case-insensitive and accent-insensitive
db.users.find().collation({ locale: "en", strength: 2 }).sort({ name: 1 })
db.users.find({ name: "ANDRÉ" }).collation({ locale: "en", strength: 1 })

Always add _id as the final sort key when order matters, both for reproducibility and because keyset pagination depends on a total order.

The sort memory limit

If no index supplies the order, the server does a blocking sort: it buffers the whole result set, then sorts. Past the limit it gives up rather than degrade quietly.

Sort exceeded memory limit of 104857600 bytes,
but did not opt in to external sorting.

The default is 100MB (older releases used 32MB, which is why you still see 33554432 in the wild). An indexed sort has no memory limit at all, because documents already arrive in order. Confirm with explain: a SORT stage in the plan means blocking, its absence means the index did the work.

skip and limit

limit is cheap. skip is not, because the server walks and discards every skipped entry before it returns anything.

db.orders.find().sort({ _id: 1 }).skip(0).limit(20)       // page 1, fast
db.orders.find().sort({ _id: 1 }).skip(100000).limit(20)  // page 5001, slow

Page N costs O(N × pageSize) work. At page 20 nobody notices, at page 5000 the query times out. skip is fine for a UI where users only ever see the first few pages, and it is the wrong tool for an export, a sync job, or an infinite scroll.

Keyset pagination

The correct pattern. Instead of counting past rows, remember the last sort key you saw and ask for what comes after it. Cost is constant per page.

// Single key, ascending on _id
let page = db.orders.find({ _id: { $gt: lastId } })
                    .sort({ _id: 1 }).limit(20).toArray();

// Compound sort, newest first, with _id as the tiebreaker
const last = page[page.length - 1];
db.orders.find({
  $or: [
    { placed_at: { $lt: last.placed_at } },
    { placed_at: last.placed_at, _id: { $lt: last._id } }
  ]
}).sort({ placed_at: -1, _id: -1 }).limit(20)

That $or is a tuple comparison written out longhand, and it is served by a single { placed_at: -1, _id: -1 } index.

A total count is a separate question. countDocuments on the same filter is exact and costs a full index scan, so either cache it or show "20+" style counts and stop pretending page numbers are free.