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. Set sorting fields as follows:
    {
        sorters: [
            {
                scoreSort: {
                    order: TableStore.SortOrder.SORT_ORDER_ASC
                }
            }
        ]
    }
  • PrimaryKeySort
    You can use PrimaryKeySort to sort the query result based on the order of primary key columns. Set sorting fields as follows:
    
    {
        sorters: [
            {
                primaryKeySort: {
                    order: TableStore.SortOrder.SORT_ORDER_DESC // Descending order                //order: TableStore.SortOrder.SORT_ORDER_ASC // Ascending order            }
            }
        ]
    }
  • FieldSort
    You can use FieldSort to sort the query result based on the order of a specified column. Set sorting fields as follows:
    {
        sorters: [
            {
                fieldSort: {
                    fieldName: "Col_Keyword",
                    order: TableStore.SortOrder.SORT_ORDER_DESC
                }
            }
        ]
    }
    You can sort the query result based on the order of multiple columns. Set sorting fields as follows:
    {
        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 the query result based on distances of geographical locations. Set sorting fields as follows:
    {
        sorters: [
            {
                geoDistanceSort: {
                    fieldName: "Col_Geo_Point",
                    points: ["0,0"],// Set the center point                order: TableStore.SortOrder.SORT_ORDER_ASC // The query result is sorted in ascending order of the distances between geographical locations and the central point.            }
            }
        ]
    }

Specify a paging method

Only fields with the EnableSortAndAgg attribute 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 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.

    
    /**
    * Set Offset to 90 and Limit to 10 to scan data from Line 90 to Line 100. */
    client.search({
        tableName: TABLE_NAME,
        indexName: INDEX_NAME,
        searchQuery: {
            offset: 90,
            limit: 10, 
            query: {
                queryType: TableStore.QueryType.MATCH_ALL_QUERY
            }
            getTotalCount: true // If TotalCount is set to true, the total number of rows that meet the filtering conditions is returned. If TotalCount is set to false, the total number of rows that meet the filtering conditions is not returned.    },
        columnToGet: { // Set RETURN_SPECIFIED to return a specified number of columns, RETURN_ALL to return all columns, or RETURN_NONE to return no data.        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

    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. 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 if you use a token. The system scans data page by page. This results in a slow query.

    /**
    * The following code provides an example of how to use tokens to scan pages in sync and async modes. */
    var params = {
        tableName: TABLE_NAME,
        indexName: INDEX_NAME,
        searchQuery: {
            offset: 0,
            limit: 10,
            token: null,// Obtain a nextToken as the start point to scan the next page.        query: {
                queryType: TableStore.QueryType.MATCH_ALL_QUERY
            },
            getTotalCount: true
        },
        columnToGet: {
            returnType: TableStore.ColumnReturnType.RETURN_NONE,
            returnNames: ["pic_tag", "pic_description", "time_stemp", "pos"]
        }
    };
    
    /**
    * Use tokens to scan pages in sync mode. */
    (async () => { //code in sync mode.
      try {
        var data = await client.search(params);
        console.log(data);
    
        while (data.nextToken) { // If a nextToken exists, you have not obtained all the data.      params.searchQuery.token = data.nextToken;// Update the token value to continue scanning pages.      data = await client.search(params);
          console.log(data);
        }
      } catch (error) {
          console.log(error);
      }
    })()
    
    /**
    * Use tokens to scan pages in async mode. */
    client.search(params, function (err, data) { // code in async mode. Only scan one page.    console.log('success:', JSON.stringify(data, null, 2));
    
        if (data.nextToken) {
            params.searchQuery.token = data.nextToken;// Update the token value to continue scanning pages.
            client.search(params, function (err, data) {
                console.log('token success:', JSON.stringify(data, null, 2));
            });
        }
    });
    
    /**
     * Implement persistent storage for tokens. * 1) Encode nextToken as binary data in the Buffer object by using Base64 to implement persistent storage for the token. * 2) Convert Base64 strings to binary data by creating a Buffer object to use nextToken as a parameter. */
    var stringToken = data.nextToken.toString("base64", data.nextToken.offset, data.nextToken.limit);
    var bufferToken = new Buffer(stringToken, "base64");