When you use a search index to query data, you can use a predefined sorting method or specify a sorting method. This way, the rows that meet the query conditions are returned based on the order that you predefined or specified. If a large number of rows are included in the response, you can locate the required data by configuring the Limit and Offset parameters or by using tokens.
Use scenarios
Category | Method | Feature | Scenario |
Sorting | Predefine a sorting method when you create a search index | By default, data in a search index is sorted based on the presorting settings that are specified by the IndexSort parameter. The presorting settings that are specified by the IndexSort parameter determine the default order in which the rows that meet the query conditions are returned. | |
Specify a sorting method when you query data | You can use ScoreSort to sort query results based on the BM25-based keyword relevance score. ScoreSort is suitable for scenarios such as full-text search. | ||
You can use PrimaryKeySort to sort query results based on the primary key value. PrimaryKeySort is suitable for scenarios in which you want to sort data based on the unique identifiers of the data. | |||
You can use FieldSort to sort query results based on the values of one or more columns. FieldSort is suitable for scenarios in which you want to sort data based on properties such as sales volume or page views. In most cases, FieldSort is used in industries such as e-commerce and social networking and media asset. | |||
You can use GeoDistanceSort to sort query results by geographical location. GeoDistanceSort is suitable for scenarios in which you want to sort data based on the distance from a specific location. In most cases, GeoDistanceSort is used in industries such as mapping and logistics. For example, you can sort restaurants around a location based on the distance from the location. | |||
Paging | Specify a paging method when you query data | If the number of rows in the response is less than 100,000, you can use this method to jump to a page. | |
If you use this feature, data is returned page by page and you can only page backward. If you want to page forward, you can cache and use a previous token because tokens are permanently valid during a query. |
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.
You can specify PrimaryKeySort or FieldSort as the presorting method for a search index. PrimaryKeySort sorts data by primary key value and FieldSort sorts data by field value.
Search indexes that contain Nested fields do not support index presorting.
If you want to modify the settings of the IndexSort parameter for an existing search index, you can dynamically modify the schema of the search index. For more information, see Dynamically modify the schema of a search index.
Specify a sorting method when you query data
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 query results based on the BM25-based keyword relevance score. ScoreSort is suitable for scenarios such as full-text search.
You must configure the parameters for ScoreSort to sort the matched data by keyword relevance score. Otherwise, the matched data is sorted based on the presorting settings that are specified by the IndexSort parameter.
sort = Sort(
sorters=[ScoreSort(sort_order=SortOrder.DESC)]
)
client.search(
'<TABLE_NAME>', '<SEARCH_INDEX_NAME>', SearchQuery(query, sort=sort, limit=100, get_total_count=True), ColumnsToGet(return_type=ColumnReturnType.ALL)
)
PrimaryKeySort
You can use PrimaryKeySort to sort query results based on the value of the primary key.
sort = Sort(
sorters=[PrimaryKeySort(sort_order=SortOrder.DESC)]
)
client.search(
'<TABLE_NAME>', '<SEARCH_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 one or more specific columns.
Sort the query results based on the values of a single column
You can use FieldSort to sort the query results based on the values of a specified column.
sort = Sort(
sorters=[FieldSort('a', SortOrder.ASC)]
)
client.search(
'<TABLE_NAME>', '<SEARCH_INDEX_NAME>', SearchQuery(query, sort=sort, limit=100, get_total_count=True), ColumnsToGet(return_type=ColumnReturnType.ALL)
)
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.
sort = Sort(
sorters=[
FieldSort('a', SortOrder.ASC),
FieldSort('b', SortOrder.ASC)
]
)
client.search(
'<TABLE_NAME>', '<SEARCH_INDEX_NAME>', SearchQuery(query, sort=sort, limit=100, get_total_count=True), ColumnsToGet(return_type=ColumnReturnType.ALL)
)
GeoDistanceSort
You can use GeoDistanceSort to sort 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>', '<SEARCH_INDEX_NAME>', SearchQuery(query, sort=sort, limit=100, get_total_count=True), ColumnsToGet(return_type=ColumnReturnType.ALL)
)
Specify a paging method
You can use limit and offset or use tokens to split returned query results into pages.
Configure the limit and offset parameters
If the total number of rows in the response is less than 100,000, you can configure the limit and offset parameters to page the rows. The sum of the values of the limit and offset parameters cannot exceed 100,000. The maximum value of the limit parameter is 100.
For information about how to increase the maximum value of the limit parameter, see 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?
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.
query = RangeQuery('k', 'key100', 'key500', include_lower=False, include_upper=False)
search_response = client.search(
'<TABLE_NAME>', '<SEARCH_INDEX_NAME>',
SearchQuery(query, offset=100, limit=100, get_total_count=True),
ColumnsToGet(return_type=ColumnReturnType.ALL)
)
print('request_id : %s' % search_response.request_id)
print('is_all_succeed : %s' % search_response.is_all_succeed)
print('total_count : %s' % search_response.total_count)
print('rows : %s' % search_response.rows)
Use a token
We recommend that you use a token for deep paging because this method has no limits on the paging depth.
If Tablestore cannot read full data that meets the query conditions, Tablestore 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. If you want to page forward, you can cache and use a previous token because tokens are 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 field 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, which results in a slow query.
Search indexes that contain fields of the Nested type do not support IndexSort. If you require paging and use a search index that contains fields of the Nested type to query data, you must specify the sorting method in the query conditions to return data in the specified order. Otherwise, Tablestore does not return next_token when only part of the data that meets the query conditions is returned.
query = MatchAllQuery()
all_rows = []
next_token = None
# first round
search_response = client.search('<TABLE_NAME>', '<SEARCH_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>', '<SEARCH_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))
FAQ
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, geo query, Boolean query, KNN vector query, nested query, and exists query. You can use the query methods provided by the search index 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.