When you use a search index to query data, you can predefine a sorting method when you create the search index or specify a sorting method when you query data. 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.

Scenarios

Category Method Feature Scenario
Sorting Specify a sorting method when you create a search index Index presorting 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 Sorting based on the BM25-based keyword relevance score (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.
Sorting based on the value of the primary key (PrimaryKeySort) You can use PrimaryKeySort to sort query results based on the value of the primary key. PrimaryKeySort is suitable for scenarios in which you want to sort data based on the unique identifiers of the data.
Sorting based on the values of a specific column (FieldSort) You can use FieldSort to sort query results based on the values of a specific column. 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.
Sorting by geographical location (GeoDistanceSort) 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 Paging based on the limit and offset parameters If the number of rows in the response is smaller than 50,000, you can use this method to jump to a page.
Paging based on tokens 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 valid during the 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.

Important
  • You can specify PrimaryKeySort or FieldSort as the presorting method for a search index. PrimaryKeySort sorts data by primary key and FieldSort sorts data by field value.
  • Search indexes that contain fields of the Nested type do not support index presorting.

Specify a sorting method when you query data

Sorting can be enabled only for fields for which enableSortAndAgg 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.
Important 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: {
    sorters: [
        {
            scoreSort: {
                order: TableStore.SortOrder.SORT_ORDER_ASC
            }
        }
    ]
}

PrimaryKeySort

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

sort: {
    sorters: [
        {
            primaryKeySort: {
                order: TableStore.SortOrder.SORT_ORDER_DESC // Sort the query results in the descending order. 
                //order: TableStore.SortOrder.SORT_ORDER_ASC // Sort the query results in the ascending order. 
            }
        }
    ]
}

FieldSort

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

sort: {
    sorters: [
        {
            fieldSort: {
                fieldName: "Col_Keyword",
                order: TableStore.SortOrder.SORT_ORDER_DESC
            }
        }
    ]
}

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

sort: {
    sorters: [
        {
            fieldSort: {
                fieldName: "Col_Keyword",
                order: TableStore.SortOrder.SORT_ORDER_DESC
            }
        },
        {
            fieldSort: {
                fieldName: "Col_Long",
                order: TableStore.SortOrder.SORT_ORDER_DESC
            }
        }
    ]
}

GeoDistanceSort

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

sort: {
    sorters: [
        {
            geoDistanceSort: {
                fieldName: "Col_Geo_Point",
                points: ["0,0"],// Specify the coordinate pair of the central point. 
                order: TableStore.SortOrder.SORT_ORDER_ASC // The query results are returned in ascending order of the distances between geographical locations and the central point. 
            }
        }
    ]
}

For the detailed sample code, see Search.

Specify a paging method

You can configure the limit and offset parameters or use tokens to page the rows in the response.

Configure the limit and offset parameters

If the total number of rows in the response is smaller than 50,000, you can configure the limit and offset parameters to page the rows. The sum of the values of the limit and offset parameter cannot exceed 50,000. The maximum value of the limit parameter is 100.
Note 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.

/**
 * Set offset to 90 and limit to 10 to scan data from line 90 to line 99. 
 */
client.search({
    tableName: TABLE_NAME,
    indexName: INDEX_NAME,
    searchQuery: {
        offset: 90,
        limit: 10, 
        query: {
            queryType: TableStore.QueryType.MATCH_ALL_QUERY
        }
        getTotalCount: true // If getTotalCount is set to true, the total number of rows that meet the query conditions is returned. If getTotalCount is set to false, the total number of rows that meet the query conditions is not returned. 
    },
    columnToGet: { // Set columnToGet to RETURN_SPECIFIED and specify a value for RETURN_SPECIFIED to return the specified columns, set columnToGet to RETURN_ALL to return all columns, or set columnToGet to RETURN_NONE to return only the primary key columns. 
        returnType: TableStore.ColumnReturnType.RETURN_ALL
    }
}, function (err, data) {
    if (err) {
        console.log('error:', err);
        return;
    }
    console.log('success:', JSON.stringify(data, null, 2));
});

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 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 a previous token to page forward 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.

Important Search indexes that contain fields of the Nested type do not support IndexSort. If you require paging and you 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 nextToken when only part of the data that meets the query conditions is returned.
/**
 * The following code provides an example on how to use tokens to page the rows in the response in synchronous and asynchronous modes. 
 */
var params = {
    tableName: TABLE_NAME,
    indexName: INDEX_NAME,
    searchQuery: {
        offset: 0,
        limit: 10,
        token: null,// Obtain nextToken as the start point to scan the next page. The data type is byte stream. 
        query: {
            queryType: TableStore.QueryType.MATCH_ALL_QUERY
        },
        getTotalCount: true
    },
    columnToGet: {
        returnType: TableStore.ColumnReturnType.RETURN_SPECIFIED,
        returnNames: ["pic_tag", "pic_description", "time_stemp", "pos"]
    }
};

/**
 * The following code provides an example on how to use tokens to page the rows in the response in synchronous mode. 
 */
(async () => { // Code in synchronous mode. 
  try {
    var data = await client.search(params);
    console.log('success:', JSON.stringify(data, null, 2));

    while (data.nextToken && data.nextToken.length) { // If nextToken is returned, Tablestore continues reading the subsequent data. 

      // Persist the tokens. 
      //1) Convert a token (nextToken) as binary data in the Buffer object to a Base64-encoded string and persist the string. 
      //2) You can convert the persisted Base64-encoded string to binary data by creating a Buffer object to use nextToken as a parameter. 
      var nextToken = data.nextToken.toString("base64");
      var token =  Buffer.from(nextToken, "base64");

      params.searchQuery.token = token;// Update the token value to return more results. 
      data = await client.search(params);
      console.log('token success:', JSON.stringify(data, null, 2));
    }
  } catch (error) {
      console.log(error);
  }
})()

/**
 * The following code provides an example on how to use tokens to page the rows in the response in asynchronous mode. 
 */
client.search(params, function (err, data) { 
    console.log('success:', JSON.stringify(data, null, 2));

    if (data.nextToken && data.nextToken.length) {

        // Persist the tokens. 
        //1) Convert a token (nextToken) as binary data in the Buffer object to a Base64-encoded string and persist the string. 
        //2) You can convert the persisted Base64 string to binary data by creating a Buffer object to use nextToken as a parameter. 
        var nextToken = data.nextToken.toString("base64");
        var token =  Buffer.from(nextToken, "base64");

        params.searchQuery.token = token;// Update the token value to return more results. 
        client.search(params, function (err, data) {
            console.log('token success:', JSON.stringify(data, null, 2));
        });
    }
});