Introduction to MongoDb with .NET part 9: limiting, sorting and skipping

Introduction

In the previous post we looked a couple of array-related search operations in MongoDb. We saw examples for searching in arrays and sub-documents. We also went through operators such as $all and $in that are frequently used with arrays. In addition we saw the role of the dot notation to perform searches deeper down in the JSON object structure.

In this post we’ll look at various other functions related to searches: limiting, sorting and skipping that correspond to the TOP, ORDER BY and OFFSET/FETCH operators in MS SQL.

The cursor object

When we call the find() method on a collection, such as db.zipcodes.find() then it returns a cursor. It is similar to the situation when a cursor is allocated in MS SQL that you can use to iterate through the records in a search result. The cursor object has a number of methods that can be useful to refine our searches.

Limiting

It’s common to limit the search results to the top x number of items. An example would be if we want to show the 10 slowest URLs in a load test results. The limit() function does exactly what we need in this situation. It accepts an integer as follows:

db.zipcodes.find().limit(10)

The query will return the first 10 items in the collection. We can of course provide the usual search parameters to the find function we’ve seen before:

db.zipcodes.find({"pop" : {$lte : 1000}}).limit(5)

Skipping

Skipping is often used in conjunction with paging through the result set. You’ve probably seen tables that show the first 10 items and then you can click on page 2 of the table and retrieve the next 10 items and so on. Paging is a combination of skipping the first x number of items and then limiting the result set. The skip() function which also accepts an integer can be used for this purpose:

db.zipcodes.find({"pop" : {$lte : 1000}}).skip(3).limit(5)

The above query will first skip the first 3 items in the result set and then limit the results to 5 items.

Sorting

Ordering the result set is also a quite common operation. We want to sort the result for our users so that they can find what they want more easily: sort the places by the city name or the ZIP code. The sort function accepts a JSON document where we can declare which fields to sort on and in what order. Whether the ordering is ascending or descending is specified by -1 or 1 where 1 means an ascending and -1 means a descending order. Here’s an example of sorting the restaurants by name in ascending order:

db.restaurants.find().sort({"name" : 1})

String sorts are based on the UTF-8 code value of the strings.

We can sort on multiple fields which is equivalent to the THEN BY clause in MS SQL. We just need to extend the JSON input of course:

db.zipcodes.find().sort({"city" : -1, "pop" : 1})

The above query sorts the zip codes first by city in a descending order and then by population in an ascending order in case there are two identical place names such as ZEELAND and ZEPHYRHILLS.

Limiting, skipping and sorting

There’s nothing stopping us from combining these functions. Here comes an example:

db.zipcodes.find().skip(100).limit(5).sort({"city" : -1, "pop" : 1})

In the next post we’ll start looking into updates.

You can view all posts related to data storage on this blog here.