Tablestore:Sort and paginate results

Query request

The type of request is SearchRequest. The following table describes its parameters.

Query configuration

The type of request.searchQuery is SearchQuery. The following table describes only the parameters related to sorting and pagination.

Sort configuration

The type of request.searchQuery.sort is Sort. The following table describes its parameters.

Relevance score sort

ScoreSort sorts rows by the relevance score calculated by using the BM25 algorithm. The following table describes its parameter.

To sort by relevance score, explicitly configure ScoreSort. Otherwise, index sort is used.

Primary key sort

PrimaryKeySort sorts rows by primary key. The following table describes its parameter.

Field sort

FieldSort sorts rows by field value. The following table describes its parameters.

Nested filter

The type of FieldSort.nestedFilter is NestedFilter. The following table describes its parameters.

Geographical distance sort

GeoDistanceSort sorts rows by the distance between a geographical point field and target points. The following table describes its parameters.

Columns to return

The type of request.columnsToGet is SearchRequest.ColumnsToGet. Whether returned columns must be read from the data table affects the maximum limit value.

Configure index sort

The following example configures the score field as an index sort field when a search index is created. If sort is not configured for a query, results are returned in ascending order of score.

FieldSchema score = new FieldSchema("score", FieldType.LONG)
        .setEnableSortAndAgg(true);

IndexSchema indexSchema = new IndexSchema();
indexSchema.setFieldSchemas(Collections.singletonList(score));
indexSchema.setIndexSort(new Sort(
        Collections.<Sort.Sorter>singletonList(
                new FieldSort("score", SortOrder.ASC))));

Sort by relevance score

The following example returns results in descending order of BM25 relevance score.

TermQuery termQuery = new TermQuery();
termQuery.setFieldName("category");
termQuery.setTerm(ColumnValue.fromString("book"));

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(termQuery);
searchQuery.setSort(new Sort(
        Collections.<Sort.Sorter>singletonList(new ScoreSort())));

Handle missing field values

The following example sorts rows by the score field in descending order. If a row does not contain the field, the value of score_backup is used. If both fields are missing, the row is placed last.

FieldSort fieldSort = new FieldSort("score", SortOrder.DESC);
fieldSort.setMissingFields(Collections.singletonList("score_backup"));
fieldSort.setMissingValue(FieldSort.LAST_WHEN_MISSING);

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(new MatchAllQuery());
searchQuery.setSort(new Sort(
        Collections.<Sort.Sorter>singletonList(fieldSort)));

Sort multi-valued and Nested fields

When sorting an array or another multi-valued field, use mode to specify the value that participates in sorting. The following example sorts rows in descending order by the maximum value in the scores array.

FieldSort fieldSort = new FieldSort("scores", SortOrder.DESC);
fieldSort.setMode(SortMode.MAX);

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(new MatchAllQuery());
searchQuery.setSort(new Sort(
        Collections.<Sort.Sorter>singletonList(fieldSort)));

When sorting a Nested subfield, also configure the Nested path and select the child rows that participate in sorting. The following example uses only child rows where items.age is 1 and sorts rows in ascending order by the minimum value of items.name.

TermQuery ageQuery = new TermQuery();
ageQuery.setFieldName("items.age");
ageQuery.setTerm(ColumnValue.fromLong(1));

FieldSort fieldSort = new FieldSort("items.name", SortOrder.ASC);
fieldSort.setMode(SortMode.MIN);
fieldSort.setNestedFilter(new NestedFilter("items", ageQuery));

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(new MatchAllQuery());
searchQuery.setSort(new Sort(
        Collections.<Sort.Sorter>singletonList(fieldSort)));

Sort by geographical distance

The following example returns rows from nearest to farthest based on the spherical distance between the location field and 30.23,120.19.

GeoDistanceSort geoSort = new GeoDistanceSort(
        "location", Collections.singletonList("30.23,120.19"));
geoSort.setOrder(SortOrder.ASC);
geoSort.setDistanceType(GeoDistanceType.ARC);

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(new MatchAllQuery());
searchQuery.setSort(new Sort(
        Collections.<Sort.Sorter>singletonList(geoSort)));

Paginate by using limit and offset

The following example skips the first 100 rows and returns the next 100 rows. When this method is used, the sum of limit and offset cannot exceed 100,000.

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(new MatchAllQuery());
searchQuery.setLimit(100);
searchQuery.setOffset(100);

Paginate by using a token

The following example reads all results in a loop. The token is null for the first query. Each subsequent query directly uses the nextToken from the previous response. After setToken is called, the SDK clears sort because the token contains the sort conditions of the previous page.

List<Row> rows = new ArrayList<Row>();
byte[] nextToken = null;
do {
    SearchQuery searchQuery = new SearchQuery();
    searchQuery.setQuery(new MatchAllQuery());
    searchQuery.setLimit(100);
    searchQuery.setToken(nextToken);

    SearchRequest request =
            new SearchRequest("example_table", "example_index", searchQuery);
    SearchResponse response = client.search(request);
    rows.addAll(response.getRows());
    nextToken = response.getNextToken();
} while (nextToken != null);

For sequential queries in the same process, pass nextToken directly as a byte array. Use Base64 encoding only when the token must be persisted or transferred across processes or between a frontend and backend. Do not convert the token by using new String(nextToken), which corrupts the token.

String encodedToken = Base64.getEncoder().encodeToString(nextToken);
byte[] decodedToken = Base64.getDecoder().decode(encodedToken);