Tablestore:Sorting and paging

Scenarios

Index presorting

By default, data in a search index is sorted based on the presorting settings that are specified by the IndexSort parameter. When you use a search index to query data, the presorting settings that are specified by the IndexSort parameter determine the default order in which the matched data is returned.

When you create a search index, you can specify presorting settings by configuring the IndexSort parameter. If you do not specify presorting settings, data in the search index is sorted by primary key value.

Specify a sorting method when you query data

Sorting can be enabled only for fields for which the EnableSortAndAgg parameter 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

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

PrimaryKeySort

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

// Sort the query results based on the values of the primary key in ascending order. 
var searchQuery = new SearchQuery();
searchQuery.Sort = new Sort(new List<ISorter>() { new PrimaryKeySort() });

// Sort the query results based on the values of the primary key in 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 results based on the values of one or more specific columns.

• Sort the query results based on the values of a single column

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

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

• Sort the query results based on the values of multiple columns

  You can also sort query results based on the values of two columns in specific orders to determine the order in which the matched data is returned.

  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 query results by geographical location.

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

You can configure the Limit and Offset parameters or use tokens to page the rows in the response.

Paging based on the Limit and Offset parameters

If you do not specify values for the Limit and Offset parameters, the default values are used. The default value of the Limit parameter is 10. The default value of the Offset parameter is 0.

var searchQuery = new SearchQuery();
searchQuery.Query = new MatchAllQuery();
searchQuery.Limit = 100;
searchQuery.Offset = 100;

Paging based on tokens

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

If Tablestore cannot read all data that meets the query conditions, Tablestore returns NextToken. You can use NextToken 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, the sorting method is the same as the method that is used in the previous request. Tablestore sorts data based on the IndexSort parameter by default or based on the method that you specified. You cannot specify the sorting method when you use a token. You cannot configure the Offset parameter when you use a token. Data is returned page by page in sequence. This results in a slow query.

/// <summary>
/// In this example, a token is used to page the rows in the response and Tablestore 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) // If NextToken is null in the response, all data is read. 
    {
        request.SearchQuery.Token = response.NextToken;
        response = otsClient.Search(request);
        rows.AddRange(response.Rows);
    }

    return response;
}

FAQ

• What do I do if no data can be found by calling the Search operation?

• How do I increase the value of the limit parameter to 1000 when I call the Search operation of the search index feature to query data?

• Why does a token become invalid when pagination is used in a search index-based query?

References

• When you use a search index to query data, you can use the following query methods: term query, terms query, match all query, match query, match phrase query, prefix query, range query, wildcard query, Boolean query, geo query, nested query, and exists query. You can use different query methods to query data from multiple dimensions based on your business requirements.

  You can sort or paginate rows that meet the query conditions by using the sorting and paging features. For more information, see Sorting and paging.

  You can use the collapse (distinct) feature to collapse the result set based on a specific column. This way, data of the specified type appears only once in the query results. For more information, see Collapse (distinct).

• If you want to analyze data in a data table, you can use the aggregation feature of the Search operation or execute SQL statements. For example, you can obtain the minimum and maximum values, sum, and total number of rows. For more information, see Aggregation and SQL query.

• If you want to obtain all rows that meet the query conditions without the need to sort the rows, you can call the ParallelScan and ComputeSplits operations to use the parallel scan feature. For more information, see Parallel scan.