Pagination

We recommend paginating your query results if you received one of the following errors:

• Query result contains more than 100,000 rows. Please contact Support to raise this limit for you.
• Query result contains more than 100MiB. Please contact Support to raise this limit for you.

Rockset can now paginate query results if your result set exceeds either 100,000 documents or 100MiB.

Rockset recommends using async queries, which will automatically use the pagination functionality (no need to set the paginate parameter) when client_timeout_ms elapses and timeout_ms is set with a longer duration.

With pagination, the query results are stored for you. You can retrieve them later using a Rockset-provided cursor and a specified number of documents.

Using pagination, your application can then process the paginated results, one result set at a time.

Using Query Results Pagination

To use pagination, invoke the /orgs/self/queries endpoint and set the paginate parameter to true in the body of your request. It is false by default.

Request

The following example shows a request to the /orgs/self/queries endpoint to fetch the first 1000 documents:

curl --request POST \
    --url https://$ROCKSET_SERVER/v1/orgs/self/queries \
    -H 'Authorization: ApiKey $API_KEY' \
  -H 'Content-Type: application/json' \
    -d '{
    "sql": {
      "parameters": [
        {
          "name": "_id",
          "type": "string",
          "value": "85beb391"
        }
      ],
      "query": "SELECT * FROM foo where _id = :_id",
      "paginate": true,
      "initial_paginate_response_doc_count": 1000
    }
  }'

The initial_paginate_response_doc_count parameter specifies the number of documents to be returned in the response. This optional parameter's default is 10,000 documents. For example, if your application displays results to users in groups of 100, set initial_paginate_response_doc_count to 100.

Note: Setting the paginate parameter to true removes the size limit for the results. Your query will still need to complete processing under the two-minute time limit. If your query times out, consider adding a limit with fewer documents, so the query can finish processing in time.

Response

The following shows an example response from Rockset for the request above:

{
  "query_id": "5b596206-c632-4a08-8343-0c560f7ef7f1",
  "results": [
    {
      ...
    }
  ],
  ...
  "results_total_doc_count": 10000000, // This is the total number of documents included in the results.
  "pagination": {
    "current_page_doc_count": 1000,    // This will be fewer if there are no more results.
    "start_cursor": null
    "next_cursor": "aabawe153wtea352"  // This will be null if there are no more results than those returned in this response.
  }
}

The results collection contains the actual result set while the query_id and pagination fields contain all of the information necessary to fetch the next page of results (described in Fetching the Next Page of Results). In order to fetch the first set of results from the start you can omit the cursor parameter and call the /orgs/self/queries/{query_id}/pages?docs=[number of docs] endpoint with the optinal docs parameter.

Fetching the Next Page of Results

Invoke the /orgs/self/queries/{query_id}/pages?cursor={next_cursor}&docs=[number of docs] endpoint to fetch the next page of results. Retrieve the values from the query_id and next_cursor fields from the response above. Then pass them into the respective query parameters in the endpoint. The docs parameter is optional. If you choose not to add a docs parameter, the default will be 10,000 documents.

Request

The following example shows how to invoke this endpoint using the query_id and next_cursor field values returned from the previous response:

curl --request GET \
    --url https://$ROCKSET_SERVER/v1/orgs/self/queries/5b596206-c632-4a08-8343-0c560f7ef7f1/pages?cursor=aabawe153wtea352&docs=500 \
    -H 'Authorization: ApiKey <api_key>' \
  -H 'Content-Type: application/json'

Response

The following shows an example response for the request:

{
  "results": [
    {
      “Field1”: ”value1”
    },
    ...
  ],
  "results_total_doc_count": 10000000
  "pagination": {
    "current_page_doc_count": 500,
    "next_cursor_offset": 1500,    // This number is the number of documents before the current page.
    "next_cursor": fds23jurzjsa31  // This value will be null if there are no more results.
  }
}

Note: If there are no more documents after the current page, the value for next_cursor will be null.

Obtaining the Status of Paginated Queries

You can obtain the status of one or more paginated queries (e.g., to determine when they will expire).

Request

To get the status of all of the paginated queries for your organization, invoke the /orgs/self/queries endpoint:

curl --request GET \
    --url https://$ROCKSET_SERVER/v1/orgs/self/queries \
    -H 'Authorization: ApiKey <api_key>' \
  -H 'Content-Type: application/json'

Response

The following shows an example response for the request:

{
  "data": [
    {
      "query_id": "5b596206-c632-4a08-8343-0c560f7ef7f1",
      "state": "RUNNING", // or "FINISHED"
      "run_by": "xyz@rockset.com",
      "run_at": "2001-08-28T00:23:41Z",
      "expires_at": "2001-08-28T00:33:41Z",
      "stats" : {
        "elapsed_time_ms": 12000,
        "throttled_time_ms": 126,
        "doc_count": 27842,
        "size_bytes": 455223422
      },
      "pagination": {
        "start_cursor": null
      }
    },
    ... // Additional paginated queries will follow here.
  ]
}