Sort and paginate results - Tablestore SDK for Python - Tablestore

Use Tablestore SDK for Python to control the order of search index results and paginate results by using offset or next_token.

Prerequisites

Install the Tablestore SDK for Python and initialize a client.

Description

Search indexes support index presorting and query-time sorting. When you create a search index, use index_sort to specify the default order. If index_sort is not specified, rows are sorted by primary key. Index presorting supports only PrimaryKeySort and FieldSort and is not supported for an index that contains a Nested field. After creation, you can dynamically update the schema to change index presorting. At query time, use SearchQuery.sort to specify ScoreSort, PrimaryKeySort, FieldSort, or GeoDistanceSort, or combine multiple sorters in list order. Except for primary keys, sort fields must have sorting and aggregation enabled when the index is created.

Pagination method	Description
limit and offset	Use when results do not exceed 100,000 rows and you need to jump to a specified position. `limit + offset` cannot exceed `100000`.
next_token	Use for deep pagination or sequentially reading all results. Pagination depth is not subject to the 100,000-row limit, but pages can be read only sequentially.

The following example returns the first 10 rows sorted by score in descending order and then by primary key in ascending order.

sort = Sort([
    FieldSort("score", SortOrder.DESC),
    PrimaryKeySort(SortOrder.ASC),
])
response = client.search(
    "example_table",
    "example_index",
    SearchQuery(MatchAllQuery(), sort=sort, limit=10),
    ColumnsToGet(return_type=ColumnReturnType.ALL),
)
print(response.rows)

Parameters

Search request

The search method contains the following parameters.

Name	Type	Description
table_name (required)	`str`	The name of the data table.
index_name (required)	`str`	The name of the search index.
search_query (required)	`SearchQuery`	The query condition and common query configurations.
columns_to_get (optional)	`ColumnsToGet`	The return column configuration. If this parameter is not specified, only primary key columns are returned.
routing_keys (optional)	`list`	The primary key values of custom routing fields. This parameter is not required if custom routing is not configured.
timeout_s (optional)	`int`	The request timeout in seconds. If this parameter is not specified, the client-level timeout is used.

Query configuration

search_query is of the SearchQuery type and contains the following sorting and pagination parameters.

Name	Type	Description
query (required)	`Query`	The query condition.
sort (optional)	`Sort`	The query-time sort configuration. If omitted, index presorting is used. Do not specify it when using `next_token`.
offset (optional)	`int`	The offset. Default value: `0`. Do not specify it when using `next_token`.
limit (optional)	`int`	The maximum number of rows to return. Default value: `10`. The maximum is `100` if any return column must be read from the table and `1000` if all return columns are read from the search index.
next_token (optional)	`bytes`	The pagination token. Omit it in the first request and use `next_token` from the previous response in subsequent requests.
get_total_count (optional)	`bool`	Specifies whether to return the total number of matching rows. Default value: `False`.

Sort configuration

search_query.sort is of the Sort type and contains the following parameter.

Name	Type	Description
sorters (required)	`list[Sorter]`	The list of sorters. The list order determines multi-level sort priority. Supported sorter types are `ScoreSort`, `PrimaryKeySort`, `FieldSort`, and `GeoDistanceSort`.

Relevance score sort

If search_query.sort.sorters[] is of the ScoreSort type, rows are sorted by relevance score. ScoreSort contains the following parameter.

Name	Type	Description
sort_order (optional)	`SortOrder`	The sort order. Default value: `DESC`. Explicitly configure `ScoreSort` to sort by relevance score.

Primary key sort

If search_query.sort.sorters[] is of the PrimaryKeySort type, rows are sorted by primary key. PrimaryKeySort contains the following parameter.

Name	Type	Description
sort_order (optional)	`SortOrder`	The sort order. Default value: `ASC`.

Field sort

If search_query.sort.sorters[] is of the FieldSort type, rows are sorted by field value. FieldSort contains the following parameters.

Name	Type	Description
field_name (required)	`str`	The sort field name. Sorting and aggregation must be enabled for the field.
sort_order (optional)	`SortOrder`	The sort order. Default value: `ASC`.
sort_mode (optional)	`SortMode`	The value selection mode for a multi-valued field: `MIN`, `MAX`, or `AVG`.
nested_filter (optional)	`NestedFilter`	The Nested child-field sort configuration, including the Nested path and a query that selects child rows used for sorting.

Geo-distance sort

If search_query.sort.sorters[] is of the GeoDistanceSort type, rows are sorted by the distance between a geographical point and the target points. GeoDistanceSort contains the following parameters.

Name	Type	Description
field_name (required)	`str`	The name of the GeoPoint sort field.
points (required)	`list[str]`	The target points in `latitude,longitude` format.
sort_order (optional)	`SortOrder`	`ASC` sorts from nearest to farthest, and `DESC` sorts from farthest to nearest.
sort_mode (optional)	`SortMode`	The value selection mode when multiple distances exist: `MIN`, `MAX`, or `AVG`.
geo_distance_type (optional)	`GeoDistanceType`	The distance calculation method. `ARC` (default) uses a spherical model, and `PLANE` uses a planar model.
nested_filter (optional)	`NestedFilter`	The Nested child-field sort configuration.

Return columns

columns_to_get is of the ColumnsToGet type and contains the following parameters.

Name	Type	Description
column_names (optional)	`list[str]`	The names of attribute columns to return. Specify this parameter only when `return_type` is `SPECIFIED`.
return_type (optional)	`ColumnReturnType`	The return column mode. `NONE` (default) returns only primary key columns; `SPECIFIED` returns specified attribute columns; `ALL` returns all attribute columns in the table; and `ALL_FROM_INDEX` returns all stored fields in the index.

Response

The search method returns SearchResponse. The following table describes the core fields.

Field	Type	Description
rows	`list[Row]`	The rows returned by the query. The number does not exceed `limit`.
next_token	`bytes`	The token for the next page. An empty value indicates that no more data is available.
total_count	`int`	The number of matching rows. The value depends on `get_total_count`.
is_all_succeed	`bool`	Indicates whether all index partitions were queried. If the value is `False`, partial results are returned.
agg_results	`list[AggResult]`	The metric aggregation results. This field is empty if `aggs` is not configured.
group_by_results	`list[GroupByResult]`	The grouping results. This field is empty if `group_bys` is not configured.
search_hits	`list[SearchHit]`	The search hits, including extended information such as rows, relevance scores, and highlights.

An empty next_token can also indicate that the query has no deterministic sort order. total_count is the total number of matching rows, not the current-page row count.

Tuple-compatible response

Starting from Tablestore SDK for Python 5.2.0, search APIs return response objects instead of tuples. Version 5.1.0 and earlier return tuples directly. In version 5.2.1 and later, you can call SearchResponse.v1_response() to obtain a tuple compatible with earlier versions. For new code, access SearchResponse attributes directly to avoid unpacking errors if response fields are extended.

(
    rows,
    next_token,
    total_count,
    is_all_succeed,
    agg_results,
    group_by_results,
    search_hits,
) = response.v1_response()

Examples

Sort by geo distance

The following example returns results from nearest to farthest based on the spherical distance between location and 30.25,120.16.

sort = Sort([
    GeoDistanceSort(
        "location",
        ["30.25,120.16"],
        sort_order=SortOrder.ASC,
        sort_mode=SortMode.MIN,
        geo_distance_type=GeoDistanceType.ARC,
    )
])
response = client.search(
    "example_table",
    "example_index",
    SearchQuery(MatchAllQuery(), sort=sort, limit=10),
)
print(response.rows)

Paginate by using next_token

Specify the sort order in the first request. In subsequent requests, pass only next_token from the previous response and the same query condition until the token is empty.

query = MatchAllQuery()
response = client.search(
    "example_table",
    "example_index",
    SearchQuery(query, sort=Sort([PrimaryKeySort()]), limit=100),
)
all_rows = list(response.rows)

while response.next_token:
    response = client.search(
        "example_table",
        "example_index",
        SearchQuery(query, next_token=response.next_token, limit=100),
    )
    all_rows.extend(response.rows)

print(len(all_rows))

Important

When paginating by using next_token, do not specify offset, and pages cannot be skipped directly. To move backward, cache the token used for each page and query again with the token for the target page. A search index that contains a Nested field has no index presorting. Explicitly specify sort in the first request, or the server does not return next_token.

Tablestore:Sort and paginate results