Projection, sorting and pagination

conceptThe concept

Projection is the second argument to find: { name: 1, email: 1 } (inclusion) or { items: 0 } (exclusion). You cannot mix them except for _id: 0. Array helpers: { items: { $slice: 2 } }, { items: { $elemMatch: { qty: { $gte: 3 } } } }.

Sort with .sort({ total: -1, _id: 1 }). Without a sort, order is not guaranteed. Include a unique tiebreaker (usually _id) so pages are stable.

Pagination: skip(n) still walks past n documents, so page 5,000 is slow. Keyset pagination remembers the last value seen and asks for the next range: find({ createdAt: { $lt: lastSeen } }).sort({ createdAt: -1 }).limit(20), with an index on the sort field.

whyWhy it matters

Projection reduces network traffic and memory, and enables covered queries. Unstable sorting causes duplicates or gaps between pages. Deep skip is one of the most common causes of slow APIs.

Use caseReal-world scenario

An admin order list shows 20 orders per page, newest first. Instead of ?page=812 with skip(16220), the API returns nextCursor = { createdAt, _id } of the last row and the next request filters past it.

Try itExamples

Run and edit these in the interactive lesson.

db.orders.findOne({}, { orderNo: 1, total: 1, "shipping.city": 1, _id: 0 })
db.orders.findOne({}, { items: 0, payment: 0 })
db.orders.findOne({ "items.2": { $exists: true } }, { orderNo: 1, items: { $slice: 1 } })

db.products.find({}, { name: 1, rating: 1 }).sort({ rating: -1, _id: 1 }).limit(5)

// page 3 with skip
db.orders.find({}, { orderNo: 1, createdAt: 1 }).sort({ createdAt: -1, _id: -1 }).skip(40).limit(3)

// keyset: remember the last row of the previous page
const page1 = db.orders.find({}, { orderNo: 1, createdAt: 1 }).sort({ createdAt: -1, _id: -1 }).limit(3).toArray()
const last = page1[page1.length - 1]
db.orders.find({ $or: [
  { createdAt: { $lt: last.createdAt } },
  { createdAt: last.createdAt, _id: { $lt: last._id } }
] }, { orderNo: 1, createdAt: 1 }).sort({ createdAt: -1, _id: -1 }).limit(3)

db.orders.estimatedDocumentCount()
db.orders.countDocuments({ status: "delivered" })

internalsUnder the hood

A sort that an index cannot provide becomes a blocking SORT stage limited to 100 MB of memory (it spills to disk if allowDiskUse is enabled, the default since 6.0 for find). With sort + limit, the server keeps only the top N documents in memory (a top-k sort), which is much cheaper.

Avoid / preferDo this, not that

db.orders.find().sort({ createdAt: -1 }).skip(50000).limit(20)

db.orders.find({ $or: [
  { createdAt: { $lt: last.createdAt } },
  { createdAt: last.createdAt, _id: { $lt: last._id } }
] }).sort({ createdAt: -1, _id: -1 }).limit(20)

db.orders.find().sort({ total: -1 })   // no index

db.orders.createIndex({ total: -1 })
db.orders.find().sort({ total: -1 }).limit(10)

PitfallsCommon mistakes

• Mixing inclusion and exclusion ({ name: 1, items: 0 }), which is an error.
• Paginating with skip on large offsets.
• Sorting on a non-unique field only, so ties come back in a different order on each page.
• Using countDocuments() on every page view of a huge collection; consider an estimate or a cached count.

LimitsWhen not to use it

Offset pagination is fine for small, bounded lists such as an admin screen with a handful of pages. Keyset pagination cannot jump straight to page 37, so pick it for feeds and APIs, not for numbered page links.

SecuritySecurity notes

A projection is not access control: hiding a field in one API response does not stop another query from reading it. Restrict sensitive fields with views and roles. Do not let clients choose arbitrary sort or projection fields either; sorting by an unindexed field is an easy way to make the server work hard.

ChallengeTry it yourself

const page1 = db.orders.find({ status: "delivered" }, { orderNo: 1, total: 1 }).sort({ total: -1, _id: -1 }).limit(10).toArray()
const last = page1[page1.length - 1]
db.orders.find({
  status: "delivered",
  $or: [{ total: { $lt: last.total } }, { total: last.total, _id: { $lt: last._id } }]
}, { orderNo: 1, total: 1 }).sort({ total: -1, _id: -1 }).limit(10)

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.