This topic describes the operations of sorting and paging.

IndexSort

The matched data is sorted based on the IndexSort field value when search indexes are used in a query. Search indexes of the NESTED type do not support IndexSort. You can specify the IndexSort field when you create a search index. 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.

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 based on the keyword relevance score. ScoreSort is applicable to scenarios such as full-text indexing. Note that you must set ScoreSort to sort the query result based on the keyword relevance score. Otherwise, the query result is sorted based on the value of the IndexSort field.

    var searchQuery = new SearchQuery();
    searchQuery.Sort = new Sort(new List<ISorter>() { new ScoreSort() });
  • PrimaryKeySort

    You can use PrimaryKeySort to sort the query result based on the order of primary key columns.

    Ascending order: 
    var searchQuery = new SearchQuery();
    searchQuery.Sort = new Sort(new List<ISorter>() { new PrimaryKeySort() });
    
    Descending order: 
    var searchQuery = new SearchQuery();
    searchQuery.Sort = new Sort(new List<ISorter>() { new PrimaryKeySort(SortOrder.DESC) });
    
    					
  • FieldSort

    You can use FieldSort to sort the query result based on the order of a specified column.

    var searchQuery = new SearchQuery();
    var fieldSort = new FieldSort("col", SortOrder.ASC);
    searchQuery.Sort = new Sort(new List<ISorter>() { fieldSort });

    You can sort the query result based on the order of multiple columns.

    var searchQuery = new SearchQuery();
    var col1Sort = new FieldSort("col", SortOrder.ASC);
    var col2Sort = new FieldSort("co2", SortOrder.ASC);
    searchQuery.Sort = new Sort(new List<ISorter>() { col1Sort, col2Sort });
  • GeoDistanceSort

    You can use GeoDistanceSort to sort the query result based on distances of geographical locations.

    var searchQuery = new SearchQuery();
    var geoDistanceSort = new GeoDistanceSort("geoCol", new List<string>(){"0","0"});
    searchQuery.Sort = new Sort(new List<ISorter>() { geoDistanceSort });

Specify a paging method

  • Use the limit and offset parameters

    When the total number of rows to obtain is smaller 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.

    var searchQuery = new SearchQuery();
    searchQuery.Query = new MatchAllQuery();
    searchQuery.Limit = 100;
    searchQuery.Offset = 100;
  • Use a token

    If Tablestore does not complete the reading of the data that meets the filtering conditions, the server returns NextToken. You can use NextToken to continue reading the subsequent data. You cannot set the sorting method if you use a token. When you use a token, the sorting method is the same as that used in the previous request. The system sorts data based on the IndexSort field by default or based on the specified method. You also cannot set the offset parameter when a token is used. Data is scanned page by page, which results in a slow query.

    /// <summary>
    /// If you use a token for paging, the system reads all data and returns the data in a list. 
    /// </summary>
    /// <param name="otsClient"></param>
    public static SearchResponse ReadMoreRowsWithToken(OTSClient otsClient)
    {
        var searchQuery = new SearchQuery();
        searchQuery.Query = new MatchAllQuery();
    
        var request = new SearchRequest(TableName, IndexName, searchQuery);
    
        var response = otsClient.Search(request);
        var rows = response.Rows;
        while (response.NextToken !=null) { // The system stops reading when the value of NextToken is null. null indicates that the system reads all data.    
        {
            request.SearchQuery.Token = response.NextToken;
            response = otsClient.Search(request);
            rows.AddRange(response.Rows);
        }
    
        return response;
    }