You can use aggregation to obtain the minimum value, maximum value, sum, average, count and distinct count of rows, percentile statistics, and rows in each group from an aggregation operation. You can also use aggregation to group results by field value, range, geographical location, filter, or histogram, and implement nested query. You can also use multiple aggregation features to perform complex queries.

Minimum value

The aggregation method used to return the minimum value of a field. This method is similar to the SQL MIN function.

  • Parameters
    Parameter Description
    aggregationName The unique name of the aggregation operation. You can query the results of the aggregation operation based on the name.
    fieldName The name of the field used to perform the aggregation operation. Only the LONG and DOUBLE data types are supported.
    missing

    The default value for the field used for the aggregation operation on a row when the field value is empty.

    • If you do not specify a value for the missing parameter, the row is ignored.
    • If you specify a value for the missing parameter, the value of this parameter is used as the field value of the row.
  • Examples
    /**
     * The price of each product is listed in the product table. Query the minimum price of the products produced in Zhejiang. 
     * The equivalent SQL statement: SELECT min(column_price) FROM product where place_of_production = "Zhejiang". 
     */
    public void min(SyncClient client) {
        // Create a query request. 
        SearchRequest searchRequest = SearchRequest.newBuilder()
            .tableName("tableName")
            .indexName("indexName")
            .searchQuery(
                SearchQuery.newBuilder()
                    .query(QueryBuilders.term("place_of_production","Zhejiang"))
                    .limit(0) // If you are concerned only with aggregate results and not specific data, you can set limit to 0 to improve performance. 
                    .addAggregation(AggregationBuilders.min("min_agg_1", "column_price").missing(100))
                    .build())
            .build();
        // Execute the query request. 
        SearchResponse resp = client.search(searchRequest);
        // Obtain the aggregate results. 
        System.out.println(resp.getAggregationResults().getAsMinAggregationResult("min_agg_1").getValue());
    }

Maximum value

The aggregation method used to return the maximum value of a field. This method is similar to the SQL MAX function.

  • Parameters
    Parameter Description
    aggregationName The unique name of the aggregation operation. You can query the results of the aggregation operation based on the name.
    fieldName The name of the field used to perform the aggregation operation. Only the LONG and DOUBLE data types are supported.
    missing

    The default value for the field used for the aggregation operation on a row when the field value is empty.

    • If you do not specify a value for the missing parameter, the row is ignored.
    • If you specify a value for the missing parameter, the value of this parameter is used as the field value of the row.
  • Examples
    /**
     * The price of each product is listed in the product table. Query the maximum price of the products produced in Zhejiang. 
     * The equivalent SQL statement: SELECT max(column_price) FROM product where place_of_production = "Zhejiang". 
     */
    public void max(SyncClient client) {
        // Create a query request. 
        SearchRequest searchRequest = SearchRequest.newBuilder()
            .tableName("tableName")
            .indexName("indexName")
            .searchQuery(
                SearchQuery.newBuilder()
                    .query(QueryBuilders.term("place_of_production", "Zhejiang"))
                    .limit(0) // If you are concerned only with aggregate results and not specific data, you can set limit to 0 to improve performance. 
                    .addAggregation(AggregationBuilders.max("max_agg_1", "column_price").missing(0))
                    .build())
            .build();
        // Execute the query request. 
        SearchResponse resp = client.search(searchRequest);
        // Obtain the aggregate results. 
        System.out.println(resp.getAggregationResults().getAsMaxAggregationResult("max_agg_1").getValue());
    }

Sum

The aggregation method used to return the sum of all values for a numeric field. This method is similar to the SQL SUM function.

  • Parameters
    Parameter Description
    aggregationName The unique name of the aggregation operation. You can query the results of the aggregation operation based on the name.
    fieldName The name of the field used to perform the aggregation operation. Only the LONG and DOUBLE data types are supported.
    missing

    The default value for the field used for the aggregation operation on a row when the field value is empty.

    • If you do not specify a value for the missing parameter, the row is ignored.
    • If you specify a value for the missing parameter, the value of this parameter is used as the field value of the row.
  • Examples
    /**
     * The sales volume of each product is listed in the product table. Query the total number of the sold products that are produced in Zhejiang. The value of the missing parameter is set to 10. 
     * The equivalent SQL statement: SELECT sum(column_price) FROM product where place_of_production="Zhejiang". 
     */
    public void sum(SyncClient client) {
        // Create a query request. 
        SearchRequest searchRequest = SearchRequest.newBuilder()
            .tableName("tableName")
            .indexName("indexName")
            .searchQuery(
                SearchQuery.newBuilder()
                    .query(QueryBuilders.term("place_of_production", "Zhejiang"))
                    .limit(0) // If you are concerned only with aggregate results and not specific data, you can set limit to 0 to improve performance. 
                    .addAggregation(AggregationBuilders.sum("sum_agg_1", "column_number").missing(10))
                    .build())
            .build();
        // Execute the query request. 
        SearchResponse resp = client.search(searchRequest);
        // Obtain the aggregate results. 
        System.out.println(resp.getAggregationResults().getAsSumAggregationResult("sum_agg_1").getValue());
    }

Average

The aggregation method used to return the average value of all rows for a numeric field. This method is similar to the SQL AVG function.

  • Parameters
    Parameter Description
    aggregationName The unique name of the aggregation operation. You can query the results of the aggregation operation based on the name.
    fieldName The name of the field used to perform the aggregation operation. Only the LONG and DOUBLE data types are supported.
    missing

    The default value for the field used for the aggregation operation on a row when the field value is empty.

    • If you do not specify a value for the missing parameter, the row is ignored.
    • If you specify a value for the missing parameter, the value of this parameter is used as the field value of the row.
  • Examples
    /**
     * The sales volume of each product is listed in the product table. Query the average price of the products produced in Zhejiang. 
     * The equivalent SQL statement: SELECT avg(column_price) FROM product where place_of_production = "Zhejiang". 
     */
    public void avg(SyncClient client) {
        // Create a query request. 
        SearchRequest searchRequest = SearchRequest.newBuilder()
            .tableName("tableName")
            .indexName("indexName")
            .searchQuery(
                SearchQuery.newBuilder()
                    .query(QueryBuilders.term("place_of_production", "Zhejiang"))
                    .limit(0) // If you are concerned only with aggregate results and not specific data, you can set limit to 0 to improve performance. 
                    .addAggregation(AggregationBuilders.avg("avg_agg_1", "column_number"))
                    .build())
            .build();
        // Execute the query request. 
        SearchResponse resp = client.search(searchRequest);
        // Obtain the aggregate results. 
        System.out.println(resp.getAggregationResults().getAsAvgAggregationResult("avg_agg_1").getValue());
    }

Count

The aggregation method used to return the total number of values of a specified field or the total count of rows in the table. This method is similar to the SQL COUNT function.
Note You can use the following methods to query the total count of rows in a table or the count of rows that match the query conditions:
  • Use the count feature of aggregation. Set the value of count to * in the request.
  • Use the query feature to obtain the count of rows that match the query conditions. Set the setGetTotalCount parameter to true in the query. Use MatchAllQuery to obtain the total count of rows in a table.

You can use the count (the name of a column) expression to query the count of rows that contain the column in a table. This method is suitable for scenarios that involve sparse columns.

  • Parameters
    Parameter Description
    aggregationName The unique name of the aggregation operation. You can query the results of the aggregation operation based on the name.
    fieldName The name of the field used to perform the aggregation operation. Only the LONG, DOUBLE, BOOLEAN, KEYWORD, and GEOPOINT data types are supported.
  • Examples
    /**
     * Punishment records of merchants are recorded in the merchant table. You can query the number of merchants who are located in Zhejiang and have punishment records. If a merchant does not have punishment records, the specified field is empty for that merchant. 
     * The equivalent SQL statement: SELECT count(column_history) FROM product where place_of_production="Zhejiang". 
     */
    public void count(SyncClient client) {
        // Create a query request. 
        SearchRequest searchRequest = SearchRequest.newBuilder()
            .tableName("tableName")
            .indexName("indexName")
            .searchQuery(
                SearchQuery.newBuilder()
                    .query(QueryBuilders.term("place", "Zhejiang"))
                    .limit(0)
                    .addAggregation(AggregationBuilders.count("count_agg_1", "column_history"))
                    .build())
            .build();
        // Execute the query request. 
        SearchResponse resp = client.search(searchRequest);
        // Obtain the aggregate results. 
        System.out.println(resp.getAggregationResults().getAsCountAggregationResult("count_agg_1").getValue());
    }

Distinct count

The aggregation method used to return the number of distinct values for a field. This method is similar to the SQL COUNT(DISTINCT) function.
Note The number of distinct values is an approximate number.
  • When the number of rows is smaller than 10,000, the calculated result is an exact value.
  • When the count of rows is equal to or greater than 100 million, the error rate is about 2%.
  • Parameters
    Parameter Description
    aggregationName The unique name of the aggregation operation. You can query the results of the aggregation operation based on the name.
    fieldName The name of the field used to perform the aggregation operation. Only the LONG, DOUBLE, BOOLEAN, KEYWORD, and GEOPOINT data types are supported.
    missing The default value for the field used for the aggregation operation on a row when the field value is empty.
    • If you do not specify a value for the missing parameter, the row is ignored.
    • If you specify a value for the missing parameter, the value of this parameter is used as the field value of the row.
  • Examples
    /**
     * Query the number of distinct provinces from which the products come. 
     * The equivalent SQL statement: SELECT count(distinct column_place) FROM product. 
     */
    public void distinctCount(SyncClient client) {
        // Create a query request. 
        SearchRequest searchRequest = SearchRequest.newBuilder()
            .tableName("tableName")
            .indexName("indexName")
            .searchQuery(
                SearchQuery.newBuilder()
                    .query(QueryBuilders.matchAll())
                    .limit(0)
                    .addAggregation(AggregationBuilders.distinctCount("dis_count_agg_1", "column_place"))
                    .build())
            .build();
        // Execute the query request. 
        SearchResponse resp = client.search(searchRequest);
        // Obtain the aggregate results. 
        System.out.println(resp.getAggregationResults().getAsDistinctCountAggregationResult("dis_count_agg_1").getValue());
    }

Percentile statistics

A percentile indicates the relative standing of a value in a dataset. For example, when you collect statistics for the response time of each request during routine system O&M, you must consider the response time distribution by using percentiles such as p25, p50, p90, and p99.

Note To improve result accuracy, we recommend that you specify extreme percentile values such as p1 and p99. Results returned by using extreme percentile values are more accurate than results returned by using values such as p50.
  • Parameters
    Parameter Description
    aggregationName The unique name of the aggregation operation. You can query the results of the aggregation operation based on the name.
    fieldName The name of the field used to perform the aggregation operation. Only the LONG and DOUBLE data types are supported.
    percentiles The percentiles such as p50, p90, and p99. You can specify one or more percentiles.
    missing The default value for the field used for the aggregation operation on a row when the field value is empty.
    • If you do not specify a value for the missing parameter, the row is ignored.
    • If you specify a value for the missing parameter, the value of this parameter is used as the field value of the row.
  • Examples
    /**
     * Analyze the distribution of the time of responses to system requests by using percentiles. 
     */
    public  void percentilesAgg(SyncClient client) {
        // Create a query request. 
        SearchRequest searchRequest = SearchRequest.newBuilder()
            .tableName("tableName")
            .indexName("indexName")
            .searchQuery(
                SearchQuery.newBuilder()
                    .addAggregation(AggregationBuilders.percentiles("percentilesAgg", "latency")
                        .percentiles(Arrays.asList(25.0d, 50.0d, 99.0d))
                        .missing(1.0))
                    .build())
            .build();
        // Execute the query.
        SearchResponse resp = client.search(searchRequest);
        // Obtain the analysis results.
        PercentilesAggregationResult percentilesAggregationResult = resp.getAggregationResults().getAsPercentilesAggregationResult(
            "percentilesAgg");
        for (PercentilesAggregationItem item : percentilesAggregationResult.getPercentilesAggregationItems()) {
            System.out.println("key:" + item.getKey() + " value:" + item.getValue().asDouble());
        }
    }

Group by field value

The method used to group query results based on field values. The same values are grouped together. The value of each group and the number of values in each group are returned.
Note The calculated number may be slightly different from the actual number if the number of values in a group is too large.
  • Parameters
    Parameter Description
    groupByName The unique name of the aggregation operation. You can query the results of the aggregation operation based on the name.
    fieldName The name of the field used to perform the aggregation operation. Only the LONG, DOUBLE, BOOLEAN, and KEYWORD data types are supported.
    groupBySorter The sorting rules for items in a group. By default, group items are sorted in descending order. If you configure multiple sorting rules, data is sorted based on the order in which the rules are added. Supported parameters:
    • Sort by value in ascending alphabetical order
    • Sort by value in descending alphabetical order
    • Sort by row count in ascending order
    • Sort by row count in descending order
    • Sort in ascending order of the values that are obtained from sub-aggregate results
    • Sort in descending order of the values that are obtained from sub-aggregate results
    size The number of returned groups.
    subAggregation and subGroupBy The sub-aggregation. The sub-aggregation operation is performed based on the grouping results.
    • Scenario

      Query the number of products in each category and the maximum and minimum product prices in each category.

    • Method

      Group query results by product category to obtain the number of products in each category. Then, add two sub-aggregations to obtain the maximum and minimum product prices in each category.

    • Examples
      • Fruits: The number is 5. The maximum price is CNY 15. The minimum price is CNY 3.
      • Toiletries: The number is 10. The maximum price is CNY 98. The minimum price is CNY 1.
      • Electronic devices: The number is 3. The maximum price is CNY 8,699. The minimum price is CNY 2,300.
      • Other products: The number is 15. The maximum price is CNY 1,000. The minimum price is CNY 80.
  • Example 1
    /**
     * Query the number of products and the maximum and minimum product prices in each category. 
     * Example of returned results: Fruits: 5. The minimum price is CNY 3, and the maximum price is CNY 15. Toiletries: 10. The minimum price is CNY 1, and the maximum price is CNY 98. Electronic devices: 3. The minimum price is CNY 2,300, and the maximum price is CNY 8,699. Other products: 15. The minimum price is CNY 80,
     and the maximum price is CNY 1,000. 
     */
    public void groupByField(SyncClient client) {
        // Create a query request. 
        SearchRequest searchRequest = SearchRequest.newBuilder()
            .tableName("tableName")
            .indexName("indexName")
            .searchQuery(
                SearchQuery.newBuilder()
                    .query(QueryBuilders.matchAll())
                    .limit(0) 
                    .addGroupBy(GroupByBuilders
                        .groupByField("name1", "column_type")
                        .addSubAggregation(AggregationBuilders.min("subName1", "column_price"))
                        .addSubAggregation(AggregationBuilders.max("subName2", "column_price"))
                    )
                    .build())
            .build();
        // Execute the query request. 
        SearchResponse resp = client.search(searchRequest);
        // Obtain the aggregate results. 
        for (GroupByFieldResultItem item : resp.getGroupByResults().getAsGroupByFieldResult("name1").getGroupByFieldResultItems()) {
            // Display values. 
            System.out.println(item.getKey());
            // Display the number of rows. 
            System.out.println(item.getRowCount());
            // Display the minimum prices. 
            System.out.println(item.getSubAggregationResults().getAsMinAggregationResult("subName1").getValue());
            // Display the maximum prices. 
            System.out.println(item.getSubAggregationResults().getAsMaxAggregationResult("subName2").getValue());
        }
    }
  • Example 2
        /**
         * Group data based on multiple fields. 
         * Search index supports the use of nested GroupByFields instead of the GROUP BY syntax in SQL to group data based on multiple fields. 
         * The following code provides an example on how to group data based on multiple columns, which serves the same purpose as SQL statement SELECT a,d, sum(b), sum(c) FROM user group by a,d. 
         */
        public void GroupByMultiField() {
            SearchRequest searchRequest = SearchRequest.newBuilder()
                .tableName("tableName")
                .indexName("indexName")
                .returnAllColumns(true)   // Set the parameter to false and specify addColumnsToGet to improve performance. 
                //.addColumnsToGet("col_1","col_2")
                .searchQuery(SearchQuery.newBuilder()
                    .query(QueryBuilders.matchAll())   // Add filters. Filters is equivalent to the WHERE clause. You can use QueryBuilders.bool() to perform nested queries. 
                    .addGroupBy(
                        GroupByBuilders
                            .groupByField("unique name_1", "field_a")
                            .size(20)
                            .addSubGroupBy(
                                GroupByBuilders
                                    . groupByField("unique name_2", "field_d")
                                    .size(20)
                                    .addSubAggregation(AggregationBuilders.sum("unique name_3", "field_b"))
                                    .addSubAggregation(AggregationBuilders.sum("unique name_4", "field_c"))
                            )
                    )
                    .build())
                .build();
            SearchResponse response = client.search(searchRequest);
            // Query rows that meet the specified conditions. 
            List<Row> rows = response.getRows();
            // Obtain the aggregate results. 
            GroupByFieldResult groupByFieldResult1 = response.getGroupByResults().getAsGroupByFieldResult("unique identifier_1");
            for (GroupByFieldResultItem resultItem : groupByFieldResult1.getGroupByFieldResultItems()) {
                System.out.println("field_a key:" + resultItem.getKey() + " Count:" + resultItem.getRowCount());
                // Obtain the sub-aggregate results. 
                GroupByFieldResult subGroupByResult = resultItem.getSubGroupByResults().getAsGroupByFieldResult("unique identifier_2");
                for (GroupByFieldResultItem item : subGroupByResult.getGroupByFieldResultItems()) {
                    System.out.println("field_a " + resultItem.getKey() + " field_d key:" + item.getKey() + " Count:" + item.getRowCount());
                    double sumOf_field_b = item.getSubAggregationResults().getAsSumAggregationResult("unique identifier_3").getValue();
                    double sumOf_field_c = item.getSubAggregationResults().getAsSumAggregationResult("unique name_4").getValue();
                    System.out.println("sumOf_field_b:" + sumOf_field_b);
                    System.out.println("sumOf_field_c:" + sumOf_field_c);
                }
            }
        }
  • Example 3
        /**
         * Set sorting rules for aggregation. 
         * Method: Configure sorting rules for GroupBySorter. If you configure multiple sorting rules, data is sorted based on the order in which the rules are added. GroupBySorter supports sorting in ascending or descending order. 
         * By default, data is sorted in descending order of the count of rows, which is implemented by using GroupBySorter.rowCountSortInDesc(). 
         */
        public void groupByFieldWithSort(SyncClient client) {
            // Create a query request. 
            SearchRequest searchRequest = SearchRequest.newBuilder()
                .tableName("tableName")
                .indexName("indexName")
                .searchQuery(
                    SearchQuery.newBuilder()
                        .query(QueryBuilders.matchAll())
                        .limit(0)
                        .addGroupBy(GroupByBuilders
                            .groupByField("name1", "column_type")
                            //.addGroupBySorter(GroupBySorter.subAggSortInAsc("subName1")) // Sort the data in ascending order of the values that are obtained from sub-aggregate results. 
                            .addGroupBySorter(GroupBySorter.groupKeySortInAsc())           // Sort the data in ascending order of the values that are obtained from aggregate results. 
                            //.addGroupBySorter(GroupBySorter.rowCountSortInDesc())             // Sort the data in descending order of the count of rows that is obtained from aggregate results. 
                            .size(20)
                            .addSubAggregation(AggregationBuilders.min("subName1", "column_price"))
                            .addSubAggregation(AggregationBuilders.max("subName2", "column_price"))
                        )
                        .build())
                .build();
            // Execute the query request. 
            SearchResponse resp = client.search(searchRequest);
        }

Group by range

The method used to group query results based on value ranges of a field. Field values that fall within a specified range are grouped together. The number of values in each range is returned.

  • Parameters
    Parameter Description
    groupByName The unique name of the aggregation operation. You can query the results of the aggregation operation based on the name.
    fieldName The name of the field used to perform the aggregation operation. Only the LONG and DOUBLE data types are supported.
    range[double_from, double_to) The value ranges for grouping.

    The value range can start from Double.MIN_VALUE and end with Double.MAX_VALUE.

    subAggregation and subGroupBy The sub-aggregation. The sub-aggregation operation is performed based on the grouping results.

    For example, after you group query results by sales volume and then by province, you can obtain the province that has the largest proportion of sales volume in a specified range. You must specify GroupByField in GroupByRange to run this query.

  • Examples
    /**
     * Group sales volume based on ranges [0, 1000), [1000, 5000), and [5000, ∞) to obtain the sales volume in each range. 
     */
    public void groupByRange(SyncClient client) {
        // Create a query request. 
        SearchRequest searchRequest = SearchRequest.newBuilder()
            .tableName("tableName")
            .indexName("indexName")
            .searchQuery(
                SearchQuery.newBuilder()
                    .query(QueryBuilders.matchAll())
                    .limit(0)
                    .addGroupBy(GroupByBuilders
                        .groupByRange("name1", "column_number")
                        .addRange(0, 1000)
                        .addRange(1000, 5000)
                        .addRange(5000, Double.MAX_VALUE)
                    )
                    .build())
            .build();
        // Execute the query request. 
        SearchResponse resp = client.search(searchRequest);
        // Obtain the aggregate results. 
        for (GroupByRangeResultItem item : resp.getGroupByResults().getAsGroupByRangeResult("name1").getGroupByRangeResultItems()) {
    
            // Display the number of rows. 
            System.out.println(item.getRowCount());
        }
    }

Group by geographical location

The method used to group query results based on their geographical locations to a central point. Query results in distances that fall within the specified range are grouped together. The number of items in each range is returned.

  • Parameters
    Parameter Description
    groupByName The unique name of the aggregation operation. You can query the results of the aggregation operation based on the name.
    fieldName The name of the field used to perform the aggregation operation. Only the GEOPOINT data type is supported.
    origin(double lat, double lon) The longitude and latitude of the central point.

    double lat specifies the latitude of the central point. double lon specifies the longitude of the central point.

    range[double_from, double_to) The ranges for grouping. Unit: meter.

    The value range can start from Double.MIN_VALUE and end with Double.MAX_VALUE.

    subAggregation and subGroupBy The sub-aggregation. The sub-aggregation operation is performed based on the grouping results.
  • Examples
    /**
     * Group people based on their geographical locations to a Wanda Plaza to obtain the result of the number of people in each distance range. Group the distances based on ranges [0, 1000), [1000, 5000), and [5000, Double.MAX_VALUE). The distances are measured in meter. 
     */
    public void groupByGeoDistance(SyncClient client) {
        // Create a query request. 
        SearchRequest searchRequest = SearchRequest.newBuilder()
            .tableName("tableName")
            .indexName("indexName")
            .searchQuery(
                SearchQuery.newBuilder()
                    .query(QueryBuilders.matchAll())
                    .limit(0)
                    .addGroupBy(GroupByBuilders
                        .groupByGeoDistance("name1", "column_geo_point")
                        .origin(3.1, 6.5)
                        .addRange(0, 1000)
                        .addRange(1000, 5000)
                        .addRange(5000, Double.MAX_VALUE)
                    )
                    .build())
            .build();
        // Execute the query request. 
        SearchResponse resp = client.search(searchRequest);
        // Obtain the aggregate results. 
        for (GroupByGeoDistanceResultItem item : resp.getGroupByResults().getAsGroupByGeoDistanceResult("name1").getGroupByGeoDistanceResultItems()) {
           // Display the number of rows. 
            System.out.println(item.getRowCount());
        }
    }

Group by filter

The method used to filter the query results and group them together to obtain the result of the number of items that match each filter. Results are returned in the order in which the filters are added.

  • Parameters
    Parameter Description
    groupByName The unique name of the aggregation operation. You can query the results of the aggregation operation based on the name.
    filter The filters for the query. Results are returned in the order in which the filters are added.
    subAggregation and subGroupBy The sub-aggregation. The sub-aggregation operation is performed based on the grouping results.
  • Examples
    /**
     * Add the following three filters to obtain the number of items that match each filter: The sales volume exceeds 100, the production place is Zhejiang, and the description contains Hangzhou. 
     */
    public void groupByFilter(SyncClient client) {
        // Create a query request. 
        SearchRequest searchRequest = SearchRequest.newBuilder()
            .tableName("tableName")
            .indexName("indexName")
            .searchQuery(
                SearchQuery.newBuilder()
                    .query(QueryBuilders.matchAll())
                    .limit(0) 
                    .addGroupBy(GroupByBuilders
                        .groupByFilter("name1")
                        .addFilter(QueryBuilders.range("number").greaterThanOrEqual(100))
                        .addFilter(QueryBuilders.term("place","Zhejiang"))
                        .addFilter(QueryBuilders.match("text","Hangzhou"))
                    )
                    .build())
            .build();
        // Execute the query request. 
        SearchResponse resp = client.search(searchRequest);
        // Obtain the aggregate results based on the order of filters. 
        for (GroupByFilterResultItem item : resp.getGroupByResults().getAsGroupByFilterResult("name1").getGroupByFilterResultItems()) {
            // Display the number of rows. 
            System.out.println(item.getRowCount());
        }
    }

Query by histogram

The aggregation method used to group query results by specifying data intervals. Field values within the same range are grouped together. The values of each group and the number of values in each group are returned.

  • Parameters
    Parameter Description
    groupByName The unique name of the aggregation operation. You can query the results of the aggregation operation based on the name.
    fieldName The name of the field used to perform the aggregation operation. Only the LONG and DOUBLE data types are supported.
    interval The data interval used to obtain aggregate results.
    fieldRange[min,max] The range that is used together with the interval parameter to limit the number of groups. The value calculated by using the (fieldRange.max-fieldRange.min)/interval formula cannot exceed 2000.
    minDocCount The minimum count of rows. If the count of rows in a group is smaller than the minimum count of rows, the aggregate results for this group are not returned.
    missing The default value for the field used for the aggregation operation on a row when the field value is empty.
    • If you do not specify a value for the missing parameter, the row is ignored.
    • If you specify a value for the missing parameter, the value of this parameter is used as the field value of the row.
  • Examples
    /**
     * Collect statistics for the distribution of users in different age groups. 
     */
    public static void groupByHistogram(SyncClient client) {
        // Create a query request. 
        SearchRequest searchRequest = SearchRequest.newBuilder()
            .tableName("tableName")
            .indexName("indexName")
            .searchQuery(
                SearchQuery.newBuilder()
                    .addGroupBy(GroupByBuilders
                        .groupByHistogram("groupByHistogram", "age")
                        .interval(10)
                        .minDocCount(0L)
                        .addFieldRange(0, 99))
                    .build())
            .build();
        // Execute the query request. 
        SearchResponse resp = ots.search(searchRequest);
        // Obtain the results by histogram returned from the aggregation operation. 
        GroupByHistogramResult results = resp.getGroupByResults().getAsGroupByHistogramResult("groupByHistogram");
        for (GroupByHistogramItem item : results.getGroupByHistogramItems()) {
            System.out.println("key:" + item.getKey().asLong() + " value:" + item.getValue());
        }
    }

Query the rows in each group from an aggregation operation

After you group query results, you can query data in rows of each group. This method is similar to ANY_VALUE(field) in MySQL.
Note When you query the rows in each group that are obtained from the results of an aggregation operation, the returned results contain only the primary key information if the search index contains the nested, GEOPOINT, or ARRAY field. To query the rows in each group that are obtained from the result of the aggregation operation, manually query the data table. This way, you can obtain the required field.
  • Parameters
    Parameter Description
    aggregationName The unique name of the aggregation operation. You can query the results of the aggregation operation based on the name.
    limit The maximum number of rows that can be returned for each group. By default, at most one row of data is returned.
    sort The sorting method of data in groups.
    columnsToGet The fields to be returned. Only fields in search indexes are supported. ARRAY, GEOPOINT, and nested fields are not supported.
  • Examples
    /**
     * An activity application form of a school contains information such as the names of students, class, headteacher, and monitor. You want to group students by class to view the application statistics and the property information of each class. 
     * The equivalent SQL statement: SELECT className, teacher, monitor, COUNT(*) as number FROM table GROUP BY className. 
     */
    public void testTopRows(SyncClient client) {
        SearchRequest searchRequest = SearchRequest.newBuilder()
                .indexName("indexName")
                .tableName("tableName")
                .searchQuery(
                        SearchQuery.newBuilder()
                                .query(QueryBuilders.matchAll())
                                .limit(0) 
                                .addGroupBy(GroupByBuilders.groupByField("groupName", "className")
                                        .size(5)
                                        .addSubAggregation(AggregationBuilders.topRows("topRowsName")
                                                .limit(1)
                                                .sort(new Sort(Arrays.asList(new FieldSort("teacher", SortOrder.DESC)))) // Sort rows by teacher in descending order.
                                        )
                                )
                                .build())
                .addColumnsToGet(Arrays.asList("teacher", "monitor"))
                .build();
        SearchResponse resp = client.search(searchRequest);
        List<GroupByFieldResultItem> items = resp.getGroupByResults().getAsGroupByFieldResult("groupName").getGroupByFieldResultItems();
        for (GroupByFieldResultItem item : items) {
            String className = item.getKey();
            long number = item.getRowCount();
            List<Row> topRows = item.getSubAggregationResults().getAsTopRowsAggregationResult("topRowsName").getRows();
            Row row = topRows.get(0);
            String teacher = row.getLatestColumn("teacher").getValue().asString();
            String monitor = row.getLatestColumn("monitor").getValue().asString();
        }
    }

Nesting

GroupBy supports nesting. You can add sub-aggregations to a GroupBy.

Nesting is used to perform sub-aggregation operations within a group. For example, you can perform two-level nesting aggregations.
  • GroupBy + SubGroupBy: Items are grouped by province and then by city to obtain data for each city in each province.
  • GroupBy + SubAggregation: Items are grouped by province to obtain the maximum value of a metric for each province.
Note GroupBy can contain endless levels for nesting. However, to ensure the performance and facilitate GroupBy operations, you are allowed only to specify a small number of levels for nesting. For more information, see Search index limits.
Examples
/**
 * Perform nesting-based aggregation. 
 * Two Aggregations and one GroupByField are added to the outermost level. Two sub-aggregations and one GroupByRange are added to GroupByField. 
 */
public void subGroupBy(SyncClient client) {
    // Create a query request. 
    SearchRequest searchRequest = SearchRequest.newBuilder()
        .indexName("index_name")
        .tableName("table_name")
        .returnAllColumns(true)
        .searchQuery(
            SearchQuery.newBuilder()
                .query(QueryBuilders.match("textField", "hello"))
                .limit(10)
                .addAggregation(AggregationBuilders.min("name1", "fieldName1"))
                .addAggregation(AggregationBuilders.max("name2", "fieldName2"))
                .addGroupBy(GroupByBuilders
                    .groupByField("name3", "fieldName3")
                    .addSubAggregation(AggregationBuilders.max("subName1", "fieldName4"))
                    .addSubAggregation(AggregationBuilders.sum("subName2", "fieldName5"))
                    .addSubGroupBy(GroupByBuilders
                        .groupByRange("subName3", "fieldName6")
                        .addRange(12, 90)
                        .addRange(100, 900)
                    ))
                .build())
        .build();
    // Execute the query request. 
    SearchResponse resp = client.search(searchRequest);
    // Obtain the maximum and minimum values for the first level. 
    AggregationResults aggResults = resp.getAggregationResults();
    System.out.println(aggResults.getAsMinAggregationResult("name1").getValue());
    System.out.println(aggResults.getAsMaxAggregationResult("name2").getValue());

    // Obtain the GroupByField results of the first level and the results of the aggregations nested in the GroupByField. 
    GroupByFieldResult results = resp.getGroupByResults().getAsGroupByFieldResult("someName1");
    for (GroupByFieldResultItem item : results.getGroupByFieldResultItems()) {
        System.out.println("count" + item.getRowCount());
        System.out.println("key:" + item.getKey());

        // Obtain the sub-aggregate results. 
        // Display the maximum value obtained from the results of the sub-aggregation operation. 
        System.out.println(item.getSubAggregationResults().getAsMaxAggregationResult("subName1"));
        // Display the sum obtained from the results of the sub-aggregation operation. 
        System.out.println(item.getSubAggregationResults().getAsSumAggregationResult("subName2"));
        // Display the GroupByRange values obtained from the results of the sub-aggregation operation. 
        GroupByRangeResult subResults = resp.getGroupByResults().getAsGroupByRangeResult("subName3");
        for (GroupByRangeResultItem subItem : subResults.getGroupByRangeResultItems()) {
            System.out.println("count:" + subItem.getRowCount());
            System.out.println("key:" + subItem.getKey());
        }
    }
}

Multiple aggregations

You can perform multiple aggregation operations.
Note If you perform multiple complex aggregation operations at the same time, the response time may increase.
  • Example 1
    public void multipleAggregation(SyncClient client) {
        // Create a query request. 
        SearchRequest searchRequest = SearchRequest.newBuilder()
            .tableName("tableName")
            .indexName("indexName")
            .searchQuery(
                SearchQuery.newBuilder()
                    .query(QueryBuilders.matchAll())
                    .limit(0) 
                    .addAggregation(AggregationBuilders.min("name1", "long"))
                    .addAggregation(AggregationBuilders.sum("name2", "long"))
                    .addAggregation(AggregationBuilders.distinctCount("name3", "long"))
                    .build())
            .build();
        // Execute the query request. 
        SearchResponse resp = client.search(searchRequest);
        // Obtain the minimum value calculated from the results of the aggregation operation. 
        System.out.println(resp.getAggregationResults().getAsMinAggregationResult("name1").getValue());
        // Obtain the sum calculated from the results of the aggregation operation. 
        System.out.println(resp.getAggregationResults().getAsSumAggregationResult("name2").getValue());
        // Obtain the distinct count calculated from the results of the aggregation operation. 
        System.out.println(resp.getAggregationResults().getAsDistinctCountAggregationResult("name3").getValue());
    }
  • Example 2
    public void multipleGroupBy(SyncClient client) {
        // Create a query request. 
        SearchRequest searchRequest = SearchRequest.newBuilder()
            .tableName("tableName")
            .indexName("indexName")
            .searchQuery(
                SearchQuery.newBuilder()
                    .query(QueryBuilders.matchAll())
                    .limit(0)
                    .addAggregation(AggregationBuilders.min("name1", "long"))
                    .addAggregation(AggregationBuilders.sum("name2", "long"))
                    .addAggregation(AggregationBuilders.distinctCount("name3", "long"))
                    .addGroupBy(GroupByBuilders.groupByField("name4", "type"))
                    .addGroupBy(GroupByBuilders.groupByRange("name5", "long").addRange(1, 15))
                    .build())
            .build();
        // Execute the query request. 
        SearchResponse resp = client.search(searchRequest);
        // Obtain the minimum value calculated from the results of the aggregation operation. 
        System.out.println(resp.getAggregationResults().getAsMinAggregationResult("name1").getValue());
        // Obtain the sum calculated from the results of the aggregation operation. 
        System.out.println(resp.getAggregationResults().getAsSumAggregationResult("name2").getValue());
        // Obtain the distinct count calculated from the results of the aggregation operation. 
        System.out.println(resp.getAggregationResults().getAsDistinctCountAggregationResult("name3").getValue());
        // Obtain the GroupByField values from the results of the aggregation operation. 
        for (GroupByFieldResultItem item : resp.getGroupByResults().getAsGroupByFieldResult("name4").getGroupByFieldResultItems()) {
            // Display the keys. 
            System.out.println(item.getKey());
            // Display the number of rows. 
            System.out.println(item.getRowCount());
        }
        // Obtain the GroupByRange values from the results of the aggregation operation. 
        for (GroupByRangeResultItem item : resp.getGroupByResults().getAsGroupByRangeResult("name5").getGroupByRangeResultItems()) {
            // Display the number of rows. 
            System.out.println(item.getRowCount());
        }
    }