You can specify IndexSort when you create a search index and specify a sorting method when you query data. You can use limit and offset or tokens for pagination.

Index presorting

By default, data in a search index is sorted based on the value of the IndexSort parameter. When you use a search index to query data, the value of the IndexSort parameter determines the default order in which the matched data is returned.

When you create a search index, you can specify a value for the IndexSort parameter. If you do not specify a value for the IndexSort parameter, data in the search index is sorted by primary key. If you do not specify a value for the IndexSort parameter and use the search index to query data, the matched data is returned in the order of the primary key by default.

Notice Search indexes that contain fields of the Nested type do not support index presorting.

Specify a sorting method

Sorting can be enabled only for fields for which enable_sort_and_agg is set to True.

You can specify a sorting method for each query. Search index-based queries support the following sorting methods. You can also specify multiple sorting methods based on different priorities.

  • ScoreSort
    You can use ScoreSort to sort the query results based on the BM25-based keyword relevance score. ScoreSort is suitable for scenarios such as full-text search.
    Notice You must specify a value for ScoreSort to sort the matched data by keyword relevance score. Otherwise, the matched data is sorted based on the value that is specified for the IndexSort parameter.
    sort = Sort(
        sorters=[ScoreSort(sort_order=SortOrder.DESC)]
    )
    client.search(
        table_name, index_name, SearchQuery(query, sort=sort, limit=100, get_total_count=True), ColumnsToGet(return_type=ColumnReturnType.ALL)
    )
  • PrimaryKeySort

    You can use PrimaryKeySort to sort the query results based on the value of the primary key.

    sort = Sort(
        sorters=[PrimaryKeySort(sort_order=SortOrder.DESC)]
    )
    client.search(
        table_name, index_name, SearchQuery(query, sort=sort, limit=100, get_total_count=True), ColumnsToGet(return_type=ColumnReturnType.ALL)
    ) = PrimaryKeySort(sort_order=SortOrder.DESC)
  • FieldSort

    You can use FieldSort to sort the query results based on the values of a specified column.

    Sort the query result based on a non-nested field:
    sort = Sort(
        sorters=[FieldSort('l', SortOrder.ASC)]
    )
    
    client.search(
        table_name, index_name, SearchQuery(query, sort=sort, limit=100, get_total_count=True), ColumnsToGet(return_type=ColumnReturnType.ALL)
    )
    Sort the query result based on a nested field:
    sort = Sort(
        sorters=[
            FieldSort(
                'n.nl', 
                sort_order=SortOrder.ASC, 
                nested_filter=NestedFilter('n', RangeQuery('n.nl', range_from=150, range_to=200))
            )
        ]
    )
    client.search(
        table_name, index_name, SearchQuery(query, sort=sort, limit=100, get_total_count=True), ColumnsToGet(return_type=ColumnReturnType.ALL)
    )

    You can also sort values in two columns in specified orders to determine the order in which the matched data is returned.

    sort = Sort(
        sorters=[
            FieldSort('a', SortOrder.ASC),
            FieldSort('b', SortOrder.ASC)
        ]
    )
    
    client.search(
        table_name, index_name, SearchQuery(query, sort=sort, limit=100, get_total_count=True), ColumnsToGet(return_type=ColumnReturnType.ALL)
    )
  • GeoDistanceSort

    You can use GeoDistanceSort to sort the query results by geographical location.

    sort = Sort(
        sorters=[GeoDistanceSort('g', ['32.5,116.5', '32.0,116.0'], sort_order=SortOrder.DESC, sort_mode=SortMode.MAX)]
    )
    
    client.search(
        table_name, index_name, SearchQuery(query, sort=sort, limit=100, get_total_count=True), ColumnsToGet(return_type=ColumnReturnType.ALL)
    )    

Specify a pagination method

You can use limit and offset or use tokens to split returned query results into pages.

  • Use the limit and offset parameters
    When the total number of returned rows to obtain is smaller than 50,000, you can configure the limit and offset parameters to paginate the rows. The sum of the limit and offset parameter values cannot exceed 50,000. The maximum value of the limit parameter is 100.

    If you use the limit and offset parameters to paginate the rows but do not specify values, the default values are used. The default value of the limit parameter is 10. The default value of the offset parameter is 0.

    query = RangeQuery('k', 'key100', 'key500', include_lower=False, include_upper=False)
    search_response = client.search(
        table_name, index_name, 
        SearchQuery(query, offset=100, limit=100, get_total_count=True), 
        ColumnsToGet(return_type=ColumnReturnType.ALL)
    )  
  • Use a token

    We recommend that you use a token for deep pagination because this method has no limits on the pagination depth.

    If Tablestore is not able to complete reading data that meets the filter condition, the server returns next_token. You can use next_token to continue reading the subsequent data.

    By default, you can only page backward when you use a token. However, you can cache and use the previous token to page forward because a token is valid during the query.

    When you use a token for pagination, the sorting method is the same as the method that is used in the previous request. Therefore, you cannot specify the sorting method if you use a token. You cannot set the offset parameter when a token is used. Data is returned page by page in sequence, which results in a slow query.

    Notice Search indexes of the nested type do not support IndexSort. If you use a search index of the nested type to query data and require pagination, you must specify the sorting method to return data in the query conditions. Otherwise, Tablestore does not return next_token when all data that meets the query conditions is not completely read.
    query = MatchAllQuery()
    all_rows = []
    next_token = None
    # first round
    search_response = client.search(table_name, index_name,
            SearchQuery(query, next_token=next_token, limit=100, get_total_count=True),
            columns_to_get=ColumnsToGet(['k', 't', 'g', 'ka', 'la'], ColumnReturnType.SPECIFIED))
    all_rows.extend(search_response.rows)
    # loop
    while search_response.next_token:
        search_response = client.search(table_name, index_name,
            SearchQuery(query, next_token=search_response.next_token, sort=None, limit=100, get_total_count=True),
            columns_to_get=ColumnsToGet(['k', 't', 'g', 'ka', 'la'], ColumnReturnType.SPECIFIED))
        all_rows.extend(search_response.rows)
    
    print('Total rows:%d' % len(all_rows))