Tablestore:Sort and paginate results

Single-column sort

Sorts results by the values in a single column.

SearchQuery searchQuery = new SearchQuery();
searchQuery.setSort(new Sort(Arrays.asList(new FieldSort("col", SortOrder.ASC))));

Multi-column sort

Sorts results by the values in one column, and then by the values in another column.

SearchQuery searchQuery = new SearchQuery();
searchQuery.setSort(new Sort(Arrays.asList(
    new FieldSort("col1", SortOrder.ASC), new FieldSort("col2", SortOrder.ASC))));

Fallback sort

When sorting by a Long, Double, or Date column, you can use the missingField parameter to specify a fallback column of the same type. This fallback is used for any row that is missing a value in the primary sort column.

/**
* Sorts by `Col_Long` in descending order. If a row is missing a `Col_Long` value, it uses the value from `Col_Long_sec` instead.
*/
SearchQuery searchQuery = new SearchQuery();
FieldSort fieldSort = new FieldSort("Col_Long");
// Specifies `Col_Long_sec` as the fallback for missing values in `Col_Long`.
fieldSort.setMissingField("Col_Long_sec");
fieldSort.setOrder(SortOrder.DESC); 

Missing value sort

If a document is missing the sort field, you can use the missingValue parameter to control its position in the sorted results.

The sorting behavior is as follows:

• When missingValue is set to FieldSort.FIRST_WHEN_MISSING, documents with missing values are always placed at the beginning of the results, regardless of the sort order (ascending or descending).

• When missingValue is set to FieldSort.LAST_WHEN_MISSING or is not set (null), documents with missing values are always placed at the end of the results, regardless of the sort order.

  /**
   * Sorts by `Col_Long` in descending order. Places documents with a missing `Col_Long` value at the beginning of the results.
   */
  SearchQuery searchQuery = new SearchQuery();
  FieldSort fieldSort = new FieldSort("Col_Long");
  // Places documents with missing values first.
  fieldSort.setMissingValue(FieldSort.FIRST_WHEN_MISSING);
  fieldSort.setOrder(SortOrder.DESC);
  searchQuery.setSort(new Sort(Arrays.asList(fieldSort)));

Multi-value sort

For multi-valued fields, such as array or nested type fields, you can use the mode parameter to specify which value in the collection to use for sorting.

Sort by a specified value within an array.

// Rows doc1 and doc2 contain an array field named field1. In doc1, the value of field1 is [2,3]. In doc2, the value is [1,3,4].
// You can set the mode parameter to specify which value in the array to use for sorting.
{
    // If mode is set to SortMode.MAX, the results are doc2 (sorted by 4) and then doc1 (sorted by 3).
    FieldSort fieldSort = new FieldSort("field1", SortOrder.DESC);
    fieldSort.setMode(SortMode.MAX);
}
{
    // If mode is set to SortMode.MIN, the results are doc1 (sorted by 2) and then doc2 (sorted by 1).
    FieldSort fieldSort = new FieldSort("field1", SortOrder.DESC);
    fieldSort.setMode(SortMode.MIN);
}

You can also sort by values within sub-fields of a nested type.

// Rows doc1 and doc2 contain a nested type field named field1.
// In doc1, the value of field1 is [{"name":"b", "age":1},{"name":"a", "age":7}].
// In doc2, the value of field1 is [{"name":"a", "age":1},{"name":"c", "age":1},{"name":"d", "age":5}].

{
    // Sort all sub-rows and use the mode parameter to specify which value to sort by.
    // If you set mode to SortMode.MAX and sort by the age field, the result is doc1 (sorted by 7) and then doc2 (sorted by 5).
    FieldSort fieldSort = new FieldSort("field1.age", SortOrder.DESC);
    fieldSort.setMode(SortMode.MAX);
    String path = "field1";
    NestedFilter nestedFilter = new NestedFilter(path, QueryBuilders.matchAll().build());
    fieldSort.setNestedFilter(nestedFilter);
}
{
    // Sort only the sub-rows where age=1 and use the mode parameter to specify which value to sort by.
    {
        // If you set mode to SortMode.MAX and sort by the name field, the result is doc2 (sorted by "c") and then doc1 (sorted by "b").
        FieldSort fieldSort = new FieldSort("field1.name", SortOrder.DESC);
        fieldSort.setMode(SortMode.MAX);
        String path = "field1";
        NestedFilter nestedFilter = new NestedFilter(path, QueryBuilders.term("field1.age",1).build());
        fieldSort.setNestedFilter(nestedFilter);
    }
    {
        // If you set mode to SortMode.MIN and sort by the name field, the result is doc1 (sorted by "b") and then doc2 (sorted by "a").
        FieldSort fieldSort = new FieldSort("field1.name", SortOrder.DESC);
        fieldSort.setMode(SortMode.MIN);
        String path = "field1";
        NestedFilter nestedFilter = new NestedFilter(path, QueryBuilders.term("field1.age",1).build());
        fieldSort.setNestedFilter(nestedFilter);
    }
}