Collapse query results - Tablestore SDK for Java - Tablestore

Prerequisites

Install the Tablestore SDK for Java and initialize a client.

How it works

Field collapse groups matching rows by the value of a specified search index field and returns one representative row from each group. The effective sort order determines the row returned from each group. You can use sorting and pagination to configure the sort order. Field collapse changes only how query results are displayed and does not modify data in the data table.

Important

Sorting and aggregation must be enabled for the collapse field. The field must be a non-array field of the Keyword, Long, or Double type.
A collapse query supports only limit- and offset-based pagination. Token-based pagination is not supported. The sum of limit and offset cannot exceed 100000.
Aggregations and group-bys operate on the matching results before collapse. The total row count is also the number of matching rows before collapse. The total number of groups after collapse cannot be obtained.

Call search to query data and configure SearchQuery.collapse to specify the collapse field.

SearchResponse search(SearchRequest request)

The following example queries all rows, collapses the results by the category field, and sorts rows by the price field in descending order. The row with the highest price in each category is returned.

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(new MatchAllQuery());
searchQuery.setLimit(10);
searchQuery.setCollapse(new Collapse("category"));
searchQuery.setSort(new Sort(
        Arrays.asList(new FieldSort("price", SortOrder.DESC))));

SearchRequest.ColumnsToGet columnsToGet =
        new SearchRequest.ColumnsToGet();
columnsToGet.setColumns(Arrays.asList("category", "price"));

SearchRequest request =
        new SearchRequest("example_table", "example_index", searchQuery);
request.setColumnsToGet(columnsToGet);

SearchResponse response = client.search(request);
System.out.println(response.getRows());

Parameters

Query request

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

Name	Type	Description
tableName (required)	String	The name of the data table.
indexName (required)	String	The name of the search index.
searchQuery (required)	SearchQuery	The query condition and collapse configuration.
columnsToGet (optional)	SearchRequest.ColumnsToGet	The columns to return. If this parameter is not configured, only primary key columns are returned.
timeoutInMillisecond (optional)	int	The request-level query timeout in milliseconds. Default value: `-1`, which indicates that no separate query timeout is configured.
routingValues (optional)	`List<PrimaryKey>`	The primary key values of custom routing fields. You do not need to configure this parameter if custom routing is not used.

Query configuration

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

Name	Type	Description
query (required)	Query	The query condition. Search index query types are supported.
collapse (required)	Collapse	The collapse configuration.
offset (optional)	Integer	The group position from which the current query starts. Default value: `0`. The sum of this parameter and `limit` cannot exceed `100000`.
limit (optional)	Integer	The maximum number of groups to return. Default value: `10`. If this parameter is `0`, no rows are returned. The maximum value is `1000` if all returned columns are read from the search index, or `100` if any returned column must be read from the data table.
highlight (optional)	Highlight	The summary and highlighting configuration. Whether highlighted results are returned depends on the query type and index field configuration.
sort (optional)	Sort	The result sort order, which determines the representative row returned from each group and the order of groups. If this parameter is not configured, index sort is used.
trackTotalCount (optional)	int	The expected maximum number of matching rows to count. Default value: `TRACK_TOTAL_COUNT_DISABLED`, which disables counting. Set the value to `TRACK_TOTAL_COUNT` to count all matching rows before collapse. A smaller value provides better query performance.
filter (optional)	SearchFilter	The filter applied to the results of `query`.
aggregationList (optional)	`List<Aggregation>`	The aggregation configurations. Aggregations operate on the matching results before collapse.
groupByList (optional)	`List<GroupBy>`	The group-by configurations. Group-bys operate on the matching results before collapse.
token (optional)	byte[]	The pagination token. Do not configure this parameter when field collapse is used.

Collapse configuration

The type of request.searchQuery.collapse is Collapse. The following table describes its parameter.

Name	Type	Description
fieldName (required)	String	The name of the collapse field. Sorting and aggregation must be enabled for the field, and the field must be a non-array field of the Keyword, Long, or Double type.

Columns to return

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

Name	Type	Description
columns (optional)	`List<String>`	The names of the attribute columns to return. Configure this parameter only if both `returnAll` and `returnAllFromIndex` are `false`. If this parameter is not configured, only primary key columns are returned.
returnAll (optional)	boolean	Specifies whether to return all attribute columns in the data table. Default value: `false`.
returnAllFromIndex (optional)	boolean	Specifies whether to return all stored attribute columns in the search index. Default value: `false`. Do not set both this parameter and `returnAll` to `true`.

Response

The search method returns SearchResponse. The following table describes the fields related to field collapse.

Name	Type	Description
totalCount	long	The number of matching rows before collapse. Call `getTotalCount()` to obtain the value. The value depends on `trackTotalCount` and does not indicate the number of groups after collapse.
rows	`List<Row>`	The rows after collapse. Call `getRows()` to obtain the value. At most one row is returned for each distinct collapse field value.
searchHits	`List<SearchHit>`	The search hits after collapse. Call `getSearchHits()` to obtain the value.
isAllSuccess	boolean	Indicates whether all index partitions were queried. Call `isAllSuccess()` to obtain the value. If this field is `false`, the response contains partial results.
aggregationResults	AggregationResults	The aggregation results calculated from matching rows before collapse. Call `getAggregationResults()` to obtain the value. This field is returned only if `aggregationList` is configured.
groupByResults	GroupByResults	The group-by results calculated from matching rows before collapse. Call `getGroupByResults()` to obtain the value. This field is returned only if `groupByList` is configured.