MongoDB - Sorting Records

MongoDB - Sorting Documents

Sorting documents is a fundamental database operation. In this chapter, we’ll explore how to sort query results in MongoDB using modern best practices.

The sort() Method

To sort documents returned by a find() query, you chain the sort() method. This method takes a document that specifies the field(s) to sort by and the sort order. Use 1 for ascending order and -1 for descending order.

Syntax

db.collectionName.find(query).sort({ field1: 1, field2: -1, ... })

Example: Basic and Multi-field Sorting

First, let’s insert some sample data into a products collection. Note that we use modern BSON types, such as NumberInt for integer values.

// Ensure the collection is empty for a clean test
db.products.drop();

// Insert sample documents using insertMany
db.products.insertMany([
  { name: "Laptop Pro", category: "Electronics", price: 1200, stock: 15 },
  { name: "Wireless Mouse", category: "Accessories", price: 25, stock: 200 },
  { name: "Mechanical Keyboard", category: "Accessories", price: 75, stock: 50 },
  { name: "4K Monitor", category: "Electronics", price: 400, stock: 30 },
  { name: "Webcam HD", category: "Accessories", price: 75, stock: 0 }
]);

Now, let’s sort these products by price in descending order (most expensive first):

> db.products.find().sort({ price: -1 })

// Result:
[
  { _id: ..., name: 'Laptop Pro', category: 'Electronics', price: 1200, stock: 15 },
  { _id: ..., name: '4K Monitor', category: 'Electronics', price: 400, stock: 30 },
  { _id: ..., name: 'Mechanical Keyboard', category: 'Accessories', price: 75, stock: 50 },
  { _id: ..., name: 'Webcam HD', category: 'Accessories', price: 75, stock: 0 },
  { _id: ..., name: 'Wireless Mouse', category: 'Accessories', price: 25, stock: 200 }
]

For multi-field sorting, MongoDB sorts in the order the fields are listed. Let’s sort by category (ascending) and then by price (descending) within each category:

> db.products.find().sort({ category: 1, price: -1 })

// Result:
[
  { _id: ..., name: 'Mechanical Keyboard', category: 'Accessories', price: 75, stock: 50 },
  { _id: ..., name: 'Webcam HD', category: 'Accessories', price: 75, stock: 0 },
  { _id: ..., name: 'Wireless Mouse', category: 'Accessories', price: 25, stock: 200 },
  { _id: ..., name: 'Laptop Pro', category: 'Electronics', price: 1200, stock: 15 },
  { _id: ..., name: '4K Monitor', category: 'Electronics', price: 400, stock: 30 }
]

Performance and Indexing

Sorting can be a resource-intensive operation, especially on large datasets. If a sort operation is not supported by an index, MongoDB must perform a “blocking sort” in memory. If this operation requires more than 32 megabytes of RAM, the query will fail.

To ensure efficient sorting, you should create an index that matches the sort key(s). For the multi-field sort above, the ideal index would be:

> db.products.createIndex({ category: 1, price: -1 })

With this index, MongoDB can read the documents in their pre-sorted order directly from the index, which is significantly faster and avoids the in-memory sort limit.

Sorting with the Aggregation Pipeline

For more complex scenarios, sorting is often done as a stage within the aggregation pipeline using the $sort operator. This allows you to transform data before sorting it.

// Example: Find all accessories, sort them by price, and project only name and price
db.products.aggregate([
  { $match: { category: "Accessories" } },
  { $sort: { price: -1 } },
  { $project: { _id: 0, name: 1, price: 1 } }
])

// Result:
[
  { name: 'Mechanical Keyboard', price: 75 },
  { name: 'Webcam HD', price: 75 },
  { name: 'Wireless Mouse', price: 25 }
]

Common Errors & Debugging

• Exceeding Memory Limit: If you see an error like Sort operation used more than the maximum 32MB of RAM, it’s a clear sign you are missing an appropriate index for your sort query.
• Case Sensitivity: Field names in the sort document are case-sensitive and must exactly match the fields in your documents.
• Debugging with explain(): To verify if your sort is using an index, use the explain() method: db.products.find().sort({ price: -1 }).explain('executionStats'). Look for a FETCH stage that uses an IXSCAN (index scan) and ensure there is no SORT stage in the plan, which indicates an efficient, indexed sort.