Sorting, pagination, and aggregation

Sorting

In Mongoose, sorting is implemented using the sort() method. This method accepts an object as a parameter, specifying the field to sort by and the order. 1 indicates ascending order, and -1 indicates descending order. For example, sorting by creation time in descending order:

const users = await User.find().sort({ createdAt: -1 });

Multi-field sorting is also common. For example, sorting by age in ascending order first, then by name in descending order:

const users = await User.find()
  .sort({ age: 1, name: -1 });

For string sorting, case sensitivity should be considered. The collation option can be used:

const users = await User.find()
  .sort({ name: 1 })
  .collation({ locale: 'en', strength: 2 });

Pagination

Pagination is typically implemented using the skip() and limit() methods. skip() specifies the number of documents to skip, and limit() restricts the number of documents returned. For example, to fetch the second page of data with 10 items per page:

const page = 2;
const perPage = 10;
const users = await User.find()
  .skip((page - 1) * perPage)
  .limit(perPage);

For large datasets, skip() can be inefficient. An alternative is cursor-based pagination:

// First page
const firstPage = await User.find().sort({ _id: 1 }).limit(10);

// Get the ID of the last document
const lastId = firstPage[firstPage.length - 1]._id;

// Second page
const secondPage = await User.find({ _id: { $gt: lastId } })
  .sort({ _id: 1 })
  .limit(10);

Aggregation

Mongoose's aggregate() method provides powerful data aggregation capabilities. A basic aggregation pipeline consists of multiple stages:

const result = await User.aggregate([
  { $match: { age: { $gte: 18 } } },  // Filter stage
  { $group: { _id: "$city", count: { $sum: 1 } } },  // Group stage
  { $sort: { count: -1 } }  // Sort stage
]);

Common aggregation operations include:

• $group: Grouping and statistics
• $project: Field projection
• $unwind: Unwinding arrays
• $lookup: Join queries

For example, calculating the average age per city:

const stats = await User.aggregate([
  { $group: {
    _id: "$city",
    avgAge: { $avg: "$age" },
    minAge: { $min: "$age" },
    maxAge: { $max: "$age" }
  }},
  { $sort: { avgAge: -1 } }
]);

Combined Usage

Sorting, pagination, and aggregation are often used together. For example, implementing a paginated aggregation query:

const page = 1;
const perPage = 5;

const result = await Order.aggregate([
  { $match: { status: "completed" } },
  { $group: {
    _id: "$product",
    totalSales: { $sum: "$amount" },
    count: { $sum: 1 }
  }},
  { $sort: { totalSales: -1 } },
  { $skip: (page - 1) * perPage },
  { $limit: perPage }
]);

Performance Optimization

When handling large datasets, performance considerations are important:

1. Create indexes for frequently queried fields:

UserSchema.index({ age: 1, name: 1 });

2. Avoid returning unnecessary fields:

User.find().select('name age -_id');

3. Use explain() to analyze queries:

const explanation = await User.find().sort({ age: 1 }).explain();

Practical Example

Implementation of a product list for an e-commerce platform:

async function getProducts(category, page = 1, sortBy = 'popular') {
  const perPage = 12;
  let sortOption = {};
  
  switch(sortBy) {
    case 'price_asc': sortOption = { price: 1 }; break;
    case 'price_desc': sortOption = { price: -1 }; break;
    case 'newest': sortOption = { createdAt: -1 }; break;
    default: sortOption = { salesCount: -1 };
  }

  const [products, total] = await Promise.all([
    Product.find({ category })
      .sort(sortOption)
      .skip((page - 1) * perPage)
      .limit(perPage),
    Product.countDocuments({ category })
  ]);

  return {
    products,
    totalPages: Math.ceil(total / perPage),
    currentPage: page
  };
}