OFFSET LIMIT clause in Azure Cosmos DB

The OFFSET LIMIT clause is an optional clause to skip then take some number of values from the query. The OFFSET count and the LIMIT count are required in the OFFSET LIMIT clause.

When OFFSET LIMIT is used in conjunction with an ORDER BY clause, the result set is produced by doing skip and take on the ordered values. If no ORDER BY clause is used, it will result in a deterministic order of values.

Syntax

OFFSET <offset_amount> LIMIT <limit_amount>

Arguments

  • <offset_amount>

    Specifies the integer number of items that the query results should skip.

  • <limit_amount>

    Specifies the integer number of items that the query results should include

Remarks

Both the OFFSET count and the LIMIT count are required in the OFFSET LIMIT clause. If an optional ORDER BY clause is used, the result set is produced by doing the skip over the ordered values. Otherwise, the query will return a fixed order of values.

The RU charge of a query with OFFSET LIMIT will increase as the number of terms being offset increases. For queries that have multiple pages of results, we typically recommend using continuation tokens. Continuation tokens are a "bookmark" for the place where the query can later resume. If you use OFFSET LIMIT, there is no "bookmark". If you wanted to return the query's next page, you would have to start from the beginning.

You should use OFFSET LIMIT for cases when you would like to skip items entirely and save client resources. For example, you should use OFFSET LIMIT if you want to skip to the 1000th query result and have no need to view results 1 through 999. On the backend, OFFSET LIMIT still loads each item, including those that are skipped. The performance advantage is a savings in client resources by avoiding processing items that are not needed.

Examples

For example, here's a query that skips the first value and returns the second value (in order of the resident city's name):

    SELECT f.id, f.address.city
    FROM Families f
    ORDER BY f.address.city
    OFFSET 1 LIMIT 1

The results are:

    [
      {
        "id": "AndersenFamily",
        "city": "Seattle"
      }
    ]

Here's a query that skips the first value and returns the second value (without ordering):

    SELECT f.id, f.address.city
    FROM Families f
    OFFSET 1 LIMIT 1

The results are:

    [
      {
        "id": "WakefieldFamily",
        "city": "Seattle"
      }
    ]

Next steps