⏩Pagination

Skip - Limit

Skip & Limit - $skip & $limit

Skip and Limit operators are operators commonly used for pagination purposes. In most cases they are used together along with a $sort operator.

Operator	Description
Skip	Skips a specified number of documents and pass the result to the next stage
Limit	Limits the number of documents passed to the next state

The sample creates a pagination result on User documents by skipping 3 documents and returning 3 documents at a time. The results are sorted by DateOfBirth.

C#

Limit_skip.cs

var usersCollection = personsDatabase
    .GetCollection<User>(Constants.UsersCollection);

var topLevelProjection = Builders<User>.Projection
    .Exclude(u => u.Id)
    .Include(u => u.UserName)
    .Include(u => u.DateOfBirth);

var topLevelProjectionResults = await usersCollection
    .Find(Builders<User>.Filter.Empty)
    .Project(topLevelProjection)
    .SortBy(u => u.DateOfBirth)
    .Skip(skipSize)
    .Limit(limitSize)
    .ToListAsync();

Bson

Shell

User

To fetch the next page result just change the skip size. The same query can also be built using LINQ as follow:

var usersQueryableCollection = personsDatabase
    .GetCollection<User>(Constants.UsersCollection)
    .AsQueryable();
    
var linqTopLevelResults = await usersQueryableCollection
    .Select(u => new { u.UserName, u.DateOfBirth })
    .OrderBy(u => u.DateOfBirth)
    .Skip(skipSize)
    .Take(limitSize)
    .ToListAsync();

Try to avoid skipping multiple documents because $skip operator can become quite slow when used with large numbers and result sets. Instead, implement pagination logic by using a filter expression based on the last document retrieved. Example: Assume that you paginate employee documents based on their hire date and each page contains 100 employee results.

{
	"employee" : "Irene.OKon85",
	"hireDate" : ISODate("2010-05-02T11:47:36.734+02:00")
},

/* 2 */
{
	"employee" : "Kristopher.Brown",
	"hireDate" : ISODate("2015-12-15T13:41:54.000+02:00")
}

Instead of skipping each (page -1 * 100) documents try to use the last employee's hire date to fetch the next page results, meaning fetch the first limit-size documents where hireDate is greater than (or whatever condition you want) the previous page last document's hireDate. Of course this is not always possible as it fits more for previous-next ⏮️ ⏭️ pagination type, for example what happens if you want to go from the 1st page directly to then 5th page

Paginate array field

Paginating an array field requires at least an extra $unwind stage to deconstruct the array. The sample creates a pagination on the FavoriteSports of the first User document that contains more than 10 items in its favorite sports array field.

C#

Limit_skip.cs

var usersQueryableCollection = personsDatabase
    .GetCollection<User>(Constants.UsersCollection)
    .AsQueryable();

var user = await usersCollection
    .Find(u => u.FavoriteSports.Count > 10)
    .FirstOrDefaultAsync();

var sliceQuery = usersQueryableCollection
    .Where(u => u.Id == user.Id)
    .SelectMany(u => u.FavoriteSports, (u, s) => new
    {
        id = u.Id,
        sport = s
    })
    .OrderBy(u => u.sport)
    .Skip(skipSize)
    .Take(limitSize)
    .GroupBy(q => q.id)
    .Select(g => new
    {
        id = g.Key,
        sports = g.Select(a => a.sport)
    });

Shell

Result

User

LINQ explanation

1. Where clause creates a $match stage to match the user document

2. SelectMany creates an $unwind stage and deconstructs the favoriteSports array. This will produce a new document for each favorite sport in the array

3. new {} creates a $project stage that passes only the _id and each sport element from the preview stage

4. OrderBy sorts the documents alphabetically based on the sports

5. Skip/Take paginate the documents which already sorted by the sports

6. GroupBy/Select group the documents together in order to create an array of the selected sports containing in the documents retrieved from the previous stage