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

Sorting requires enableSortAndAgg to be set to true on the fields you want to sort by.

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

Use ScoreSort to rank results by BM25 relevance score, in ascending or descending order.

sort: {
    sorters: [
        {
            scoreSort: {
                order: TableStore.SortOrder.SORT_ORDER_ASC
            }
        }
    ]
}

PrimaryKeySort

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

Use PrimaryKeySort to order results by primary key value.

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

FieldSort

Use FieldSort to sort results by one or more column values.

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

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.

Use GeoDistanceSort to rank results by distance from a central geographic point.

sort: {
    sorters: [
        {
            geoDistanceSort: {
                fieldName: "Col_Geo_Point",
                points: ["0,0"],// Specify the coordinate pair of the central point.
                order: TableStore.SortOrder.SORT_ORDER_ASC // Return results in ascending order of distance.
            }
        }
    ]
}

For a complete example, see Search on GitHub.

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 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.

Use offset and limit to jump directly to any page. This method supports result sets of up to 100,000 rows.

/**
 * Set offset to 90 and limit to 10 to retrieve rows 90–99.
 */
client.search({
    tableName: TABLE_NAME,
    indexName: INDEX_NAME,
    searchQuery: {
        offset: 90,
        limit: 10,
        query: {
            queryType: TableStore.QueryType.MATCH_ALL_QUERY
        },
        getTotalCount: true // Return the total number of matching rows. Default: false.
    },
    columnToGet: {
        // RETURN_ALL: return all columns.
        // RETURN_SPECIFIED: return specified columns.
        // RETURN_NONE: return primary key columns only.
        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 the previous token to page forward because a token is valid during the query.

Token-based paging returns results page by page using a cursor (NextToken). Each response includes a token for the next page. Tokens are valid for the duration of the query, so cache a previous token to navigate back to an earlier page.

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.

The following examples show token-based paging in both synchronous and asynchronous modes. Both use the same initial params object.

var params = {
    tableName: TABLE_NAME,
    indexName: INDEX_NAME,
    searchQuery: {
        offset: 0,
        limit: 10,
        token: null, // Set to nextToken from the previous response to fetch the next page.
        query: {
            queryType: TableStore.QueryType.MATCH_ALL_QUERY
        },
        getTotalCount: true
    },
    columnToGet: {
        returnType: TableStore.ColumnReturnType.RETURN_SPECIFIED,
        returnNames: ["pic_tag", "pic_description", "time_stemp", "pos"]
    }
};

/**
 * Synchronous mode: await each page before fetching the next.
 */
(async () => {
  try {
    var data = await client.search(params);
    console.log('success:', JSON.stringify(data, null, 2));

    while (data.nextToken && data.nextToken.length) {
      // Encode the binary token as Base64 for storage or transfer,
      // then decode it back to binary before passing it as the next token.
      var nextToken = data.nextToken.toString("base64");
      var token = Buffer.from(nextToken, "base64");

      params.searchQuery.token = token;
      data = await client.search(params);
      console.log('token success:', JSON.stringify(data, null, 2));
    }
  } catch (error) {
      console.log(error);
  }
})()

/**
 * Asynchronous mode: use callbacks to fetch the next page.
 */
client.search(params, function (err, data) {
    console.log('success:', JSON.stringify(data, null, 2));

    if (data.nextToken && data.nextToken.length) {
        // Encode and decode the token the same way as in synchronous mode.
        var nextToken = data.nextToken.toString("base64");
        var token = Buffer.from(nextToken, "base64");

        params.searchQuery.token = token;
        client.search(params, function (err, data) {
            console.log('token success:', JSON.stringify(data, null, 2));
        });
    }
});