This topic describes the sorting and paging methods in search index-based queries.

IndexSort

The matched data is sorted based on the IndexSort field value when search indexes are used in a query. IndexSort determines the order of the data that Tablestore returns in search index-based queries.

If you do not specify the IndexSort field, Tablestore returns the query result based on the order of primary key columns. You can specify a sorting method when you create a search index.

Note Search indexes of the NESTED type do not support IndexSort.

Specify a sorting method

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 with different priorities as needed.

  • ScoreSort

    You can use ScoreSort to sort the query result by keyword relevance score. ScoreSort is applicable to scenarios such as full-text indexing.

    Note Note that you must set ScoreSort to sort the query result by keyword relevance score. Otherwise, the query result is sorted based on the value of the IndexSort field.
    Set sorting fields as follows:
    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 result based on the order of primary key columns.

    Set sorting fields as follows:
    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 result based on the order of a specified column.

    Set sorting fields as follows:
    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 NESTED fields as follows:
    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 sort the query result based on the order of multiple columns.

    Set sorting fields as follows:
    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 result based on distances of geographical locations.

    Set sorting fields as follows:
    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 paging method

Only fields with enable_sort_and_agg set to true can be sorted. You can set multiple filtering conditions for index sorting.

  • Use the limit and offset parameters
    When the total number of rows to obtain is less than 2,000, you can specify the limit and offset parameters for paging. The sum of the limit and offset parameter values cannot exceed 2,000.
    query = RangeQuery('k', 'key100', 'key500', include_lower=False, include_upper=False)
    rows, next_token, total_count, is_all_succeed = client.search(
        table_name, index_name, 
        SearchQuery(query, offset=100, limit=100, get_total_count=True), 
        ColumnsToGet(return_type=ColumnReturnType.ALL)
    )      
  • Use a token
    If Tablestore does not complete reading the required data, Tablestore returns next_token, which can be used to continue reading the subsequent data. When you use a token for paging, the sorting method is the same as that used in the previous request. If a token is used, the offset parameter cannot be used.
    query = MatchAllQuery()
    all_rows = []
    next_token = None
    # first round
    rows, next_token, total_count, is_all_succeed = 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(rows)
    # loop
    while next_token:
        rows, next_token, total_count, is_all_succeed = client.search(table_name, index_name,
            SearchQuery(query, next_token=next_token, sort=None, limit=100, get_total_count=True),
            columns_to_get=ColumnsToGet(['k', 't', 'g', 'ka', 'la'], ColumnReturnType.SPECIFIED))
        all_rows.extend(rows)
    
    print 'Total rows:', len(all_rows)