Aggregation - Tablestore SDK for Java - Tablestore - Alibaba Cloud Documentation Center

You can use the Tablestore SDK for Java to calculate metrics or group search index query results, including by using histograms, nested group-bys, and top rows in groups.

Prerequisites

Install the Tablestore SDK for Java and initialize a client.

How it works

After a search index query is complete, aggregation calculates metrics or groups all matching rows. Metric aggregations calculate the minimum, maximum, sum, average, count, distinct count, or percentiles of fields. Group-bys group rows by field values, multiple fields, numeric ranges, geographic distances, filters, numeric intervals, date intervals, or geographic grids. You can also add metric aggregations or group-bys within a group.

Category	Configuration type	Description
Metric aggregation	MinAggregation	Returns the minimum value of a field, similar to `MIN` in SQL.
Metric aggregation	MaxAggregation	Returns the maximum value of a field, similar to `MAX` in SQL.
Metric aggregation	SumAggregation	Returns the sum of a numeric field, similar to `SUM` in SQL.
Metric aggregation	AvgAggregation	Returns the average value of a field, similar to `AVG` in SQL.
Metric aggregation	CountAggregation	Returns the number of rows in which a specified field has a value, similar to `COUNT(field)` in SQL.
Metric aggregation	DistinctCountAggregation	Returns the number of distinct values in a field, similar to `COUNT(DISTINCT field)` in SQL.
Metric aggregation	PercentilesAggregation	Returns one or more percentiles of a field.
Metric aggregation	TopRowsAggregation	Returns the first several rows in each group based on a specified order.
Group-by	GroupByField	Groups rows by the value of one field.
Group-by	GroupByComposite	Groups rows by multiple fields and supports pagination tokens.
Group-by	GroupByRange	Groups rows by numeric ranges.
Group-by	GroupByGeoDistance	Groups rows by distance ranges from a central point.
Group-by	GroupByFilter	Groups rows by multiple filters.
Group-by	GroupByHistogram	Creates a histogram by using fixed numeric intervals.
Group-by	GroupByDateHistogram	Creates a histogram by using fixed date or time intervals.
Group-by	GroupByGeoGrid	Groups rows by GeoHash grid.

Important

Sorting and aggregation must be enabled for a search index field used in an aggregation. Supported field types vary by aggregation type. For information about search index field types and their mappings to data table field types, see Data types.
Aggregations operate on query matches. A request that contains aggregations is more complex than a request that only queries rows. If you do not need rows in the response, set limit to 0.
Distinct count, percentiles, and field group-bys use approximate calculations. A distinct count below 10,000 is close to an exact value. At a distinct count of 100 million, the error is approximately 2%. Percentiles near the ends are typically more accurate. For example, P1 and P99 are typically more accurate than P50. Parallel calculation of field group-bys may also introduce a small error.
You can combine multiple aggregations. A large number of aggregations or deep nesting increases request complexity and may increase latency. For nesting limits, see Search index limits.

Call search to query data. Configure SearchQuery.aggregationList for metric aggregations and SearchQuery.groupByList for group-bys.

SearchResponse search(SearchRequest request)

The following example queries all rows in a search index, calculates the minimum, maximum, sum, average, count, number of distinct categories, and P50 of prices, and groups rows by category.

SearchQuery searchQuery = SearchQuery.newBuilder()
        .query(QueryBuilders.matchAll())
        .limit(0)
        .addAggregation(AggregationBuilders.min("min_price", "price"))
        .addAggregation(AggregationBuilders.max("max_price", "price"))
        .addAggregation(AggregationBuilders.sum("sum_price", "price"))
        .addAggregation(AggregationBuilders.avg("avg_price", "price"))
        .addAggregation(AggregationBuilders.count("price_count", "price"))
        .addAggregation(AggregationBuilders.distinctCount(
                "category_count", "category"))
        .addAggregation(AggregationBuilders.percentiles(
                "price_percentiles", "price")
                .percentiles(Arrays.asList(50.0)))
        .addGroupBy(GroupByBuilders.groupByField(
                "category_group", "category").size(10))
        .build();

SearchRequest request =
        new SearchRequest("example_table", "example_index", searchQuery);
SearchResponse response = client.search(request);

AggregationResults aggregationResults = response.getAggregationResults();
System.out.println(aggregationResults
        .getAsMinAggregationResult("min_price").getValue());
System.out.println(aggregationResults
        .getAsMaxAggregationResult("max_price").getValue());
System.out.println(aggregationResults
        .getAsSumAggregationResult("sum_price").getValue());
System.out.println(aggregationResults
        .getAsAvgAggregationResult("avg_price").getValue());
System.out.println(aggregationResults
        .getAsCountAggregationResult("price_count").getValue());
System.out.println(aggregationResults
        .getAsDistinctCountAggregationResult("category_count").getValue());
System.out.println(aggregationResults
        .getAsPercentilesAggregationResult("price_percentiles")
        .getPercentilesAggregationItems());

GroupByFieldResult groupResult = response.getGroupByResults()
        .getAsGroupByFieldResult("category_group");
for (GroupByFieldResultItem item :
        groupResult.getGroupByFieldResultItems()) {
    System.out.println(item.getKey() + ": " + item.getRowCount());
}

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 aggregation configurations.
columnsToGet (optional)	SearchRequest.ColumnsToGet	The columns to return. This parameter applies only when `TopRowsAggregation` returns rows in groups. 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 the parameters related to aggregation.

Name	Type	Description
query (required)	Query	The query condition that determines the aggregation scope. To aggregate all rows in a search index, use `MatchAllQuery`.
aggregationList (optional)	`List<Aggregation>`	The metric aggregation configurations. Configure at least one of this parameter and `groupByList`.
groupByList (optional)	`List<GroupBy>`	The group-by configurations. Configure at least one of this parameter and `aggregationList`.
limit (optional)	Integer	The maximum number of rows to return. Default value: `10`. Set this parameter to `0` if you need only aggregation results.
offset (optional)	Integer	The row position from which the query starts. Default value: `0`.
sort (optional)	Sort	The sort order of query results. This parameter does not change the scope of metric aggregations or regular group-bys.
trackTotalCount (optional)	int	The expected maximum number of matching rows to count. If this parameter is set to `TRACK_TOTAL_COUNT`, you can obtain the total number of query matches from `SearchResponse.totalCount`.
filter (optional)	SearchFilter	The filter applied to the results of `query`. Aggregations operate on the filtered results.

Metric aggregations

Add the following parameter objects to request.searchQuery.aggregationList[]. aggName identifies the corresponding result and must be unique in a request.

MinAggregation, MaxAggregation, and AvgAggregation

Name	Type	Description
aggName (required)	String	The name of the aggregation.
fieldName (required)	String	The name of the aggregation field. Long, Double, and Date fields are supported.
missing (optional)	ColumnValue	The value used if `fieldName` is missing. If this parameter is not configured, rows in which the field is missing are ignored.

SumAggregation

Name	Type	Description
aggName (required)	String	The name of the aggregation.
fieldName (required)	String	The name of the aggregation field. Long and Double fields are supported.
missing (optional)	ColumnValue	The value used in the sum if `fieldName` is missing. If this parameter is not configured, rows in which the field is missing are ignored.

CountAggregation

Name	Type	Description
aggName (required)	String	The name of the aggregation.
fieldName (required)	String	The field whose non-null values are counted. Long, Double, Boolean, Keyword, Date, IP, and Geo-point fields are supported. Rows in a sparse column that do not contain the field are not counted.

To count all query matches, configure trackTotalCount in SearchQuery and read SearchResponse.totalCount. To count all rows in a search index, use MatchAllQuery.

DistinctCountAggregation

Name	Type	Description
aggName (required)	String	The name of the aggregation.
fieldName (required)	String	The field whose distinct values are counted. Long, Double, Boolean, Keyword, Date, IP, and Geo-point fields are supported.
missing (optional)	ColumnValue	The value used for distinct count if `fieldName` is missing. If this parameter is not configured, rows in which the field is missing are ignored.

PercentilesAggregation

Name	Type	Description
aggName (required)	String	The name of the aggregation.
fieldName (required)	String	The name of the aggregation field. Long, Double, and Date fields are supported.
percentiles (required)	`List<Double>`	The percentiles to calculate, such as `25.0`, `50.0`, `90.0`, and `99.0`.
missing (optional)	ColumnValue	The value used in percentile calculation if `fieldName` is missing. If this parameter is not configured, rows in which the field is missing are ignored.

TopRowsAggregation

Use TopRowsAggregation as a sub-aggregation of a group-by.

Name	Type	Description
aggName (required)	String	The name of the aggregation.
limit (optional)	Integer	The maximum number of rows to return from each group. Default value: `1`.
sort (optional)	Sort	The sort order of rows in a group.

The request.columnsToGet parameter controls the attribute columns that are returned. To return attribute columns directly from the search index, store the fields when you create the search index. If no columns are specified, only primary keys are returned.

Group-bys

Add the following parameter objects to request.searchQuery.groupByList[]. groupByName identifies the corresponding result and must be unique in a request.

GroupByField

Name	Type	Description
groupByName (required)	String	The name of the group-by.
fieldName (required)	String	The name of the grouping field. Long, Double, Boolean, Keyword, Date, and IP fields are supported.
size (optional)	Integer	The number of groups to return. Default value: `10`. Maximum value: `2000`.
minDocCount (optional)	Long	The minimum number of rows in a group. Groups with fewer rows are not returned.
groupBySorters (optional)	`List<GroupBySorter>`	The group sort rules. By default, groups are sorted by row count in descending order. Multiple rules take effect in the order in which they are added.
subAggregations (optional)	`List<Aggregation>`	The metric aggregations calculated within each group.
subGroupBys (optional)	`List<GroupBy>`	The group-bys applied within each parent group.

groupBySorters[] supports the following values.

Value	Description
groupKeySortInAsc	Sorts groups by key in lexicographically ascending order.
groupKeySortInDesc	Sorts groups by key in lexicographically descending order.
rowCountSortInAsc	Sorts groups by row count in ascending order.
rowCountSortInDesc	Sorts groups by row count in descending order. This is the default.
subAggSortInAsc	Sorts groups by the value of a specified sub-aggregation in ascending order.
subAggSortInDesc	Sorts groups by the value of a specified sub-aggregation in descending order.

GroupByComposite

Name	Type	Description
groupByName (required)	String	The name of the group-by.
sources (required)	`List<GroupBy>`	The grouping sources for multiple fields. Up to 32 fields are supported. Sources can be `GroupByField`, `GroupByHistogram`, or `GroupByDateHistogram`. A field source can specify its name, field, and sort order. A numeric histogram source can also specify an interval, and a date histogram source can additionally specify a time zone. A source can be sorted only by key. The default order is descending. If a field is missing, the corresponding key is `null`.
nextToken (optional)	String	The pagination token for the next page of groups. Omit this parameter in the first request. If `nextToken` in the response is not empty, use the value unchanged in the next request.
size (optional)	Integer	The number of groups to return. Default value: `10`. Maximum value: `2000`. In most cases, use this parameter to limit the number of groups.
suggestedSize (optional)	Integer	A soft limit for high-throughput integrations with compute engines such as Spark and Presto. You can set this parameter to `-1` or a value greater than the server limit. The actual number returned is `min(suggestedSize, server-side group limit, total groups)`. Do not configure this parameter and `size` in the same request.
subAggregations (optional)	`List<Aggregation>`	The sub-aggregations.
subGroupBys (optional)	`List<GroupBy>`	The sub-group-bys. `GroupByComposite` cannot itself be used as a sub-group-by.

Note

The Tablestore SDK for Java represents nextToken as a string. If you persist or transfer the token, do not modify its contents.

GroupByRange

Name	Type	Description
groupByName (required)	String	The name of the group-by.
fieldName (required)	String	The name of the grouping field. Long and Double fields are supported.
ranges (required)	`List<Range>`	The ranges. Each range is left-closed and right-open: `[from, to)`. You can use `Double.MIN_VALUE` and `Double.MAX_VALUE` as boundaries.
subAggregations (optional)	`List<Aggregation>`	The sub-aggregations.
subGroupBys (optional)	`List<GroupBy>`	The sub-group-bys.

GroupByGeoDistance

Name	Type	Description
groupByName (required)	String	The name of the group-by.
fieldName (required)	String	The name of the grouping field. Only Geo-point fields are supported.
origin (required)	GeoPoint	The central point. The constructor parameters are latitude followed by longitude. The latitude range is `[-90,+90]`, and the longitude range is `[-180,+180]`.
ranges (required)	`List<Range>`	The distance ranges in meters. Each range is left-closed and right-open: `[from, to)`.
subAggregations (optional)	`List<Aggregation>`	The sub-aggregations.
subGroupBys (optional)	`List<GroupBy>`	The sub-group-bys.

GroupByFilter

Name	Type	Description
groupByName (required)	String	The name of the group-by.
filters (required)	`List<Query>`	The filters. Results are returned in the order in which filters are added.
subAggregations (optional)	`List<Aggregation>`	The sub-aggregations.
subGroupBys (optional)	`List<GroupBy>`	The sub-group-bys.

GroupByHistogram

Name	Type	Description
groupByName (required)	String	The name of the group-by.
fieldName (required)	String	The name of the grouping field. Long and Double fields are supported.
interval (required)	ColumnValue	The histogram interval.
fieldRange (optional)	FieldRange	The aggregation range, which contains `min` and `max`. The value of `(max-min)/interval` cannot exceed `2000`.
offset (optional)	ColumnValue	The offset of bucket boundaries from the default starting point.
minDocCount (optional)	Long	The minimum number of rows in a bucket. Buckets with fewer rows are not returned.
missing (optional)	ColumnValue	The value used in the histogram if `fieldName` is missing. If this parameter is not configured, rows in which the field is missing are ignored.
groupBySorters (optional)	`List<GroupBySorter>`	The bucket sort rules.
subAggregations (optional)	`List<Aggregation>`	The sub-aggregations.
subGroupBys (optional)	`List<GroupBy>`	The sub-group-bys.

GroupByDateHistogram

Important

Date histogram aggregation is supported by Tablestore SDK for Java 5.16.1 and later. The Date field type for search indexes is supported by Tablestore SDK for Java 5.13.9 and later. For version information, see Tablestore SDK for Java version history.

Name	Type	Description
groupByName (required)	String	The name of the group-by.
fieldName (required)	String	The name of the grouping field. Only Date fields are supported.
interval (required)	DateTimeValue	The date or time interval, which consists of a value and a `DateTimeUnit`.
fieldRange (optional)	FieldRange	The aggregation range, which contains `min` and `max`. The value of `(max-min)/interval` cannot exceed `2000`.
minDocCount (optional)	Long	The minimum number of rows in a bucket. Buckets with fewer rows are not returned.
missing (optional)	ColumnValue	The date value used in the histogram if `fieldName` is missing. If this parameter is not configured, rows in which the field is missing are ignored.
timeZone (optional)	String	The time zone in the `+hh:mm` or `-hh:mm` format, such as `+08:00`. If the Date field format does not include time zone information, configure this parameter to prevent time offset in aggregation results.
groupBySorters (optional)	`List<GroupBySorter>`	The bucket sort rules.
subAggregations (optional)	`List<Aggregation>`	The sub-aggregations.
subGroupBys (optional)	`List<GroupBy>`	The sub-group-bys.

GroupByGeoGrid

Name	Type	Description
groupByName (required)	String	The name of the group-by.
fieldName (required)	String	The name of the grouping field. Only Geo-point fields are supported.
precision (required)	GeoHashPrecision	The GeoHash grid precision. Values range from `GHP_5009KM_4992KM_1`, which is approximately 5,009 km × 4,992 km, to `GHP_37MM_19MM_12`, which is approximately 37 mm × 19 mm. A larger suffix specifies a smaller grid.
size (optional)	Integer	The number of grid groups to return.
subAggregations (optional)	`List<Aggregation>`	The sub-aggregations.
subGroupBys (optional)	`List<GroupBy>`	The sub-group-bys.

Response

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

Name	Type	Description
aggregationResults	AggregationResults	The metric aggregation results. Call `getAggregationResults()` to obtain the value, and use an aggregation name to obtain a specific result type.
groupByResults	GroupByResults	The group-by results. Call `getGroupByResults()` to obtain the value, and use a group-by name to obtain a specific result type.
totalCount	long	The number of query matches. Call `getTotalCount()` to obtain the value. The value depends on `trackTotalCount`.
isAllSuccess	boolean	Indicates whether all index partitions were queried. Call `isAllSuccess()` to obtain the value. If this field is `false`, aggregation results may be incomplete.

Metric aggregation results

Configuration type	Result type	Result field and accessor
MinAggregation	MinAggregationResult	`value` is a `double`. Call `getAsMinAggregationResult(aggName).getValue()`.
MaxAggregation	MaxAggregationResult	`value` is a `double`. Call `getAsMaxAggregationResult(aggName).getValue()`.
SumAggregation	SumAggregationResult	`value` is a `double`. Call `getAsSumAggregationResult(aggName).getValue()`.
AvgAggregation	AvgAggregationResult	`value` is a `double`. Call `getAsAvgAggregationResult(aggName).getValue()`.
CountAggregation	CountAggregationResult	`value` is a `long`. Call `getAsCountAggregationResult(aggName).getValue()`.
DistinctCountAggregation	DistinctCountAggregationResult	`value` is a `long`. Call `getAsDistinctCountAggregationResult(aggName).getValue()`.
PercentilesAggregation	PercentilesAggregationResult	`percentilesAggregationItems` is a `List<PercentilesAggregationItem>`. Call `getAsPercentilesAggregationResult(aggName).getPercentilesAggregationItems()`. Each item contains `key` and `value`.
TopRowsAggregation	TopRowsAggregationResult	`rows` is a `List<Row>`. Call `getAsTopRowsAggregationResult(aggName).getRows()`.

Group-by results

Configuration type	Result type	Core result fields
GroupByField	GroupByFieldResult	`groupByFieldResultItems`. Each item contains `key`, `rowCount`, `subAggregationResults`, and `subGroupByResults`.
GroupByComposite	GroupByCompositeResult	`sourceNames`, `groupByCompositeResultItems`, and `nextToken`. The positions of `keys` in each item correspond to `sourceNames`.
GroupByRange	GroupByRangeResult	`groupByRangeResultItems`. Each item contains `from`, `to`, and `rowCount`.
GroupByGeoDistance	GroupByGeoDistanceResult	`groupByGeoDistanceResultItems`. Each item contains distance `from`, `to`, and `rowCount`.
GroupByFilter	GroupByFilterResult	`groupByFilterResultItems`. Each item contains `rowCount`, and the item order matches the filter order.
GroupByHistogram	GroupByHistogramResult	`groupByHistogramItems`. Each item contains bucket start `key` and row count `value`.
GroupByDateHistogram	GroupByDateHistogramResult	`groupByDateHistogramItems`. Each item contains millisecond timestamp `timestamp` and `rowCount`.
GroupByGeoGrid	GroupByGeoGridResult	`groupByGeoGridResultItems`. Each item contains GeoHash `key`, `geoGrid` with top-left and bottom-right coordinates, and `rowCount`.

Examples

Use sub-aggregations and sub-group-bys

The following example groups rows by category, calculates the highest price in each category, and then groups the rows in each category by city. Group sort rules take effect in the order in which they are added.

SearchQuery searchQuery = SearchQuery.newBuilder()
        .query(QueryBuilders.matchAll())
        .limit(0)
        .addGroupBy(GroupByBuilders.groupByField(
                "category_group", "category")
                .size(10)
                .addGroupBySorter(GroupBySorter.groupKeySortInAsc())
                .addSubAggregation(AggregationBuilders.max(
                        "max_price", "price"))
                .addSubGroupBy(GroupByBuilders.groupByField(
                        "city_group", "city").size(10)))
        .build();

SearchRequest request =
        new SearchRequest("example_table", "example_index", searchQuery);
SearchResponse response = client.search(request);

GroupByFieldResult result = response.getGroupByResults()
        .getAsGroupByFieldResult("category_group");
for (GroupByFieldResultItem item :
        result.getGroupByFieldResultItems()) {
    double maxPrice = item.getSubAggregationResults()
            .getAsMaxAggregationResult("max_price")
            .getValue();
    GroupByFieldResult cityResult = item.getSubGroupByResults()
            .getAsGroupByFieldResult("city_group");
    System.out.println(item.getKey() + ": " + maxPrice);
    System.out.println(cityResult.getGroupByFieldResultItems());
}

Paginate a multi-field group-by

GroupByComposite returns multi-column keys in a flat structure and supports pagination by nextToken.

GroupByComposite.Builder compositeBuilder = GroupByBuilders
        .groupByComposite("category_city_group")
        .addSources(GroupByBuilders.groupByField(
                "category", "category")
                .addGroupBySorter(GroupBySorter.groupKeySortInAsc()))
        .addSources(GroupByBuilders.groupByField(
                "city", "city")
                .addGroupBySorter(GroupBySorter.groupKeySortInAsc()))
        .size(100);

String nextToken = null;
do {
    GroupByComposite groupBy = nextToken == null
            ? compositeBuilder.build()
            : compositeBuilder.nextToken(nextToken).build();
    SearchQuery searchQuery = SearchQuery.newBuilder()
            .query(QueryBuilders.matchAll())
            .limit(0)
            .addGroupBy(groupBy)
            .build();
    SearchRequest request = new SearchRequest(
            "example_table", "example_index", searchQuery);
    SearchResponse response = client.search(request);

    GroupByCompositeResult result = response.getGroupByResults()
            .getAsGroupByCompositeResult("category_city_group");
    for (GroupByCompositeResultItem item :
            result.getGroupByCompositeResultItems()) {
        System.out.println(item.getKeys() + ": " + item.getRowCount());
    }
    nextToken = result.getNextToken();
} while (nextToken != null);

Group by range, distance, and filter

The following code shows the core configurations of three group-by types. You can combine them in the same SearchQuery.

GroupByRange priceRanges = GroupByBuilders
        .groupByRange("price_ranges", "price")
        .addRange(0, 100)
        .addRange(100, 500)
        .build();

GroupByGeoDistance distanceRanges = GroupByBuilders
        .groupByGeoDistance("distance_ranges", "location")
        .origin(30.2741, 120.1551)
        .addRange(0, 10000)
        .addRange(10000, 100000)
        .build();

GroupByFilter categoryFilters = GroupByBuilders
        .groupByFilter("category_filters")
        .addFilter(QueryBuilders.term("category", "books"))
        .addFilter(QueryBuilders.term("category", "games"))
        .build();

Create numeric and date histograms

The following example groups rows by a numeric interval of 20 and a date interval of one month.

GroupByHistogram priceHistogram = GroupByBuilders
        .groupByHistogram("price_histogram", "price")
        .interval(20)
        .offset(0)
        .minDocCount(1L)
        .addFieldRange(0, 100)
        .addGroupBySorter(GroupBySorter.groupKeySortInAsc())
        .build();

GroupByDateHistogram dateHistogram = GroupByBuilders
        .groupByDateHistogram("date_histogram", "event_date")
        .interval(1, DateTimeUnit.MONTH)
        .fieldRange("2026-01-01", "2026-06-01")
        .timeZone("+08:00")
        .minDocCount(1L)
        .addGroupBySorter(GroupBySorter.groupKeySortInAsc())
        .build();

SearchQuery searchQuery = SearchQuery.newBuilder()
        .query(QueryBuilders.matchAll())
        .limit(0)
        .addGroupBy(priceHistogram)
        .addGroupBy(dateHistogram)
        .build();
SearchResponse response = client.search(new SearchRequest(
        "example_table", "example_index", searchQuery));

Group by geographic grid

The following example groups a geographic field into GeoHash grids of approximately 39 km × 19 km.

SearchQuery searchQuery = SearchQuery.newBuilder()
        .query(QueryBuilders.matchAll())
        .limit(0)
        .addGroupBy(GroupByBuilders.groupByGeoGrid(
                "geo_grid", "location")
                .precision(GeoHashPrecision.GHP_39KM_19KM_4)
                .size(100))
        .build();

SearchResponse response = client.search(new SearchRequest(
        "example_table", "example_index", searchQuery));
GroupByGeoGridResult result = response.getGroupByResults()
        .getAsGroupByGeoGridResult("geo_grid");
System.out.println(result.getGroupByGeoGridResultItems());

Return rows from groups

The following example groups rows by category and returns the row with the highest price in each category.

SearchQuery searchQuery = SearchQuery.newBuilder()
        .query(QueryBuilders.matchAll())
        .limit(0)
        .addGroupBy(GroupByBuilders.groupByField(
                "category_group", "category")
                .size(10)
                .addSubAggregation(AggregationBuilders.topRows(
                        "top_price")
                        .limit(1)
                        .sort(new Sort(Arrays.asList(
                                new FieldSort(
                                        "price", SortOrder.DESC))))))
        .build();

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);

GroupByFieldResult result = response.getGroupByResults()
        .getAsGroupByFieldResult("category_group");
for (GroupByFieldResultItem item :
        result.getGroupByFieldResultItems()) {
    List<Row> rows = item.getSubAggregationResults()
            .getAsTopRowsAggregationResult("top_price")
            .getRows();
    System.out.println(item.getKey() + ": " + rows);
}

Multi-field group-by comparison

To group by multiple fields, nest multiple GroupByField configurations or use GroupByComposite directly. Choose based on pagination requirements, response structure, and sort rules.

Item	Nested field group-bys	Composite group-by
Configuration	Add `subGroupBys` to a parent `GroupByField`.	Add multiple grouping sources to `GroupByComposite.sources`.
Number of groups	Up to 2,000 groups at each level.	Up to 2,000 groups per page.
Number of fields	Up to three nested levels.	Up to 32 fields.
Response structure	Nested by parent and child levels.	Multi-column keys are returned as a flat list.
Pagination	Not supported.	Supported by `nextToken`.
Sorting	Supports sorting by group key, row count, or sub-aggregation value.	Each grouping source supports only lexicographic sorting by key. The default order is descending.
Sub-aggregations	Supported.	Supported.
Date field compatibility	Group keys use the date format defined for the field.	Date group keys are returned as timestamp strings.