Using OFFSET / LIMIT clause in Azure Cosmos DB queries

     OFFSET LIMIT clause one of the latest additions to the Azure Cosmos DB. Skip/Take function was a big request from users and Cosmos DB team listened users and deliver this functionality. If you think Cosmos DB is missing a feature and if you have a new idea, you can use Feedback Forums to give feedback to Cosmos Db team.

     OFFSET LIMIT clause let you skip x number of results then take y numbers of values from the query. Count for OFFSET and Limit are integer and both are required. In other words, You must use LIMIT if you use OFFSET.

     Addition to OFFSET and LIMIT, you might use ORDER BY clause if you like to order the results before you start to skip data. The biggest limitation of OFFSET/LIMIT clause is; CROSS-PARTITION queries are not supported by OFFSET/LIMIT clause. Queries must use Single Partition. There is already a request to make this function work in cross-partition and Cosmos DB team has been working on it.

Let's look at some examples. In the following example, I skip 1 result then take 1 result.

SELECT * FROM c 
WHERE c.OwnerUserId = 1 
OFFSET 1 LIMIT 1

Here is some interesting stats about this query results from the Data Explorer.

     As you can see; It doesn't cost that much. 2.98 RU is pretty good. I am little bit confused about Retrieved document count. I am not sure why it is 3. I skipped 1 document and take the next document. I was expecting to see 2 for Retrieved document count. I am not sure if I got charged for that extra document or not. Overall, I think this is a great addition to Azure Cosmos DB and I can not wait to see support for Cross-Partition queries.