You can perform aggregation operations to obtain the minimum value, maximum value, sum, average, and count and distinct count of rows. You can also perform aggregation operations to group results by field value, range, geographical location, or filter. You can perform multiple aggregation operations for complex queries.
Procedure
The following figure shows the complete procedure of aggregation.
The server queries the data that meets the query conditions and performs aggregation on the data based on the request. Therefore, a request that requires aggregation is more complex than a request that does not require aggregation.
Background information
The following table describes the aggregation methods.
Method | Description |
Minimum value | The aggregation method that can be used to return the minimum value of a field. This method can be used in a similar manner as the SQL MIN function. |
Maximum value | The aggregation method that can be used to return the maximum value of a field. This method can be used in a similar manner as the SQL MAX function. |
Sum | The aggregation method that can be used to return the sum of all values for a numeric field. This method can be used in a similar manner as the SQL SUM function. |
Average value | The aggregation method that can be used to return the average of all values for a numeric field. This method is used in a similar manner as the SQL AVG function. |
Count | The aggregation method that can be used to return the total number of values for a specified field or the total number of rows in a search index. This method can be used in a similar manner as the SQL COUNT function. |
Distinct count | The aggregation method that can be used to return the number of distinct values for a field. This method can be used in a similar manner as the SQL COUNT(DISTINCT) function. |
Percentile statistics | A percentile value indicates the relative position of a value in a dataset. For example, when you collect statistics for the response time of each request during the routine O&M of your system, you must analyze the response time distribution by using percentiles such as p25, p50, p90, and p99. |
Group by field value | The aggregation method that can be used to group query results based on field values. The values that are the same are grouped together. The identical value of each group and the number of identical values in each group are returned. Note The calculated number may be different from the actual number if the number of values in a group is very large. |
Group by range | The aggregation method that can be used to group query results based on the value ranges of a field. Field values that are within a specified range are grouped together. The number of values in each range is returned. |
Group by geographical location | The aggregation method that can be used to group query results based on geographical locations to a central point. Query results in distances that are within a specified range are grouped together. The number of items in each range is returned. |
Group by filter | The aggregation method that can be used to filter the query results and group them together to obtain the number of results that match each filter. Results are returned in the order in which the filters are specified. |
Query by histogram | The aggregation method that can be used to group query results based on specific data intervals. Field values that are within the same range are grouped together. The value range of each group and the number of values in each group are returned. |
Query the rows that are obtained from the results of an aggregation operation in each group | After you group query results, you can query the rows in each group. This method can be used in a similar manner as ANY_VALUE(field) in MySQL. |
Prerequisites
- A Tablestore client is initialized. For more information, see Initialization.
- A data table is created. Data is written to the table.
- A search index is created for the data table. For more information, see Create search indexes.
Minimum value
The aggregation method that can be used to return the minimum value of a field. This method can be used in a similar manner as the SQL MIN function.
Parameters
Parameter
Description
name
The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName
The name of the field that is used to perform the aggregation operation. Only the LONG, DOUBLE, and DATE types are supported.
missing
The default value for the field that is used to perform the aggregation operation on a row when the field value is empty.
If you do not specify a value for missing, the row is ignored.
If you specify a value for missing, the value of this parameter is used as the field value of the row.
Example
let searchQuery = { offset: 0, limit: 0, query: { queryType: TableStore.QueryType.MATCH_ALL_QUERY, }, getTotalCount: false, aggs: { aggs: [ { name: "min_test", type: TableStore.AggregationType.AGG_MIN, body: { fieldName: "col_long", missing: 333, }, }, ], }, }; let params = { tableName: tableName, indexName: indexName, searchQuery: searchQuery, columnToGet: { // Specify the columns that you want to return. You can set it to RETURN_SPECIFIED to return specified columns, RETURN_ALL to return all columns, RETURN_ALL_FROM_INDEX to return all columns in the search index, or RETURN_NONE to return only the primary key columns. returnType: TableStore.ColumnReturnType.RETURN_ALL_FROM_INDEX }, timeoutMs: 30000, } client.search(params, function (err, data) { if (err) { console.log('search error:', err.toString()); } else { console.log('search success:', data); } });
Maximum value
The aggregation method that can be used to return the maximum value of a field. This method can be used in a similar manner as the SQL MAX function.
Parameters
Parameter
Description
name
The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName
The name of the field that is used to perform the aggregation operation. Only the LONG, DOUBLE, and DATE types are supported.
missing
The default value for the field that is used to perform the aggregation operation on a row when the field value is empty.
If you do not specify a value for missing, the row is ignored.
If you specify a value for missing, the value of this parameter is used as the field value of the row.
Example
let searchQuery = { offset: 0, limit: 0, query: { queryType: TableStore.QueryType.MATCH_ALL_QUERY, }, getTotalCount: false, aggs: { aggs: [ { name: "max_test", type: TableStore.AggregationType.AGG_MAX, body: { fieldName: "col_long", missing: 333, }, }, ], }, }; let params = { tableName: tableName, indexName: indexName, searchQuery: searchQuery, columnToGet: { // Specify the columns that you want to return. You can set it to RETURN_SPECIFIED to return specified columns, RETURN_ALL to return all columns, RETURN_ALL_FROM_INDEX to return all columns in the search index, or RETURN_NONE to return only the primary key columns. returnType: TableStore.ColumnReturnType.RETURN_ALL_FROM_INDEX }, timeoutMs: 30000, } client.search(params, function (err, data) { if (err) { console.log('search error:', err.toString()); } else { console.log('search success:', data); } });
Sum
The aggregation method that can be used to return the sum of all values for a numeric field. This method can be used in a similar manner as the SQL SUM function.
Parameters
Parameter
Description
name
The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName
The name of the field that is used to perform the aggregation operation. Only the LONG and DOUBLE data types are supported.
missing
The default value for the field that is used to perform the aggregation operation on a row when the field value is empty.
If you do not specify a value for missing, the row is ignored.
If you specify a value for missing, the value of this parameter is used as the field value of the row.
Example
let searchQuery = { offset: 0, limit: 0, query: { queryType: TableStore.QueryType.MATCH_ALL_QUERY, }, getTotalCount: false, aggs: { aggs: [ { name: "sum_test", type: TableStore.AggregationType.AGG_SUM, body: { fieldName: "col_long", missing: 444, }, }, ], }, }; let params = { tableName: tableName, indexName: indexName, searchQuery: searchQuery, columnToGet: { // Specify the columns that you want to return. You can set it to RETURN_SPECIFIED to return specified columns, RETURN_ALL to return all columns, RETURN_ALL_FROM_INDEX to return all columns in the search index, or RETURN_NONE to return only the primary key columns. returnType: TableStore.ColumnReturnType.RETURN_ALL_FROM_INDEX }, timeoutMs: 30000, } client.search(params, function (err, data) { if (err) { console.log('search error:', err.toString()); } else { console.log('search success:', data); } });
Average value
The aggregation method that can be used to return the average of all values for a numeric field. This method is used in a similar manner as the SQL AVG function.
Parameters
Parameter
Description
name
The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName
The name of the field that is used to perform the aggregation operation. Only the LONG, DOUBLE, and DATE types are supported.
missing
The default value for the field that is used to perform the aggregation operation on a row when the field value is empty.
If you do not specify a value for missing, the row is ignored.
If you specify a value for missing, the value of this parameter is used as the field value of the row.
Example
let searchQuery = { offset: 0, limit: 0, query: { queryType: TableStore.QueryType.MATCH_ALL_QUERY, }, getTotalCount: false, aggs: { aggs: [ { name: "avg_test", type: TableStore.AggregationType.AGG_AVG, body: { fieldName: "col_long", missing: 111, }, }, ], }, }; let params = { tableName: tableName, indexName: indexName, searchQuery: searchQuery, columnToGet: { // Specify the columns that you want to return. You can set it to RETURN_SPECIFIED to return specified columns, RETURN_ALL to return all columns, RETURN_ALL_FROM_INDEX to return all columns in the search index, or RETURN_NONE to return only the primary key columns. returnType: TableStore.ColumnReturnType.RETURN_ALL_FROM_INDEX }, timeoutMs: 30000, } client.search(params, function (err, data) { if (err) { console.log('search error:', err.toString()); } else { console.log('search success:', data); } });
Count
The aggregation method that can be used to return the total number of values for a specified field or the total number of rows in a search index. This method can be used in a similar manner as the SQL COUNT function.
You can use the following methods to query the total number of rows in a search index or the total number of rows that meet the query conditions:
Use the count feature of aggregation and specify count(*) in the request.
Use the query feature to obtain the number of rows that meet the query conditions. Set setGetTotalCount to true in the query. Use MatchAllQuery to obtain the total number of rows in a search index.
You can use the name of a column as the value of the count expression to query the number of rows that contain the column in a search index. This method is suitable for scenarios that involve sparse columns.
Parameters
Parameter
Description
name
The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName
The name of the field that is used to perform the aggregation operation. Only the LONG, DOUBLE, BOOLEAN, KEYWORD, GEO_POINT, and DATE types are supported.
Example
let searchQuery = { offset: 0, limit: 0, query: { queryType: TableStore.QueryType.MATCH_ALL_QUERY, }, getTotalCount: false, aggs: { aggs: [ { name: "count_test", type: TableStore.AggregationType.AGG_COUNT, body: { fieldName: "col_long", }, }, ], }, }; let params = { tableName: tableName, indexName: indexName, searchQuery: searchQuery, columnToGet: { // Specify the columns that you want to return. You can set it to RETURN_SPECIFIED to return specified columns, RETURN_ALL to return all columns, RETURN_ALL_FROM_INDEX to return all columns in the search index, or RETURN_NONE to return only the primary key columns. returnType: TableStore.ColumnReturnType.RETURN_ALL_FROM_INDEX }, timeoutMs: 30000, } client.search(params, function (err, data) { if (err) { console.log('search error:', err.toString()); } else { console.log('search success:', data); } });
Distinct count
The aggregation method that can be used to return the number of distinct values for a field. This method can be used in a similar manner as the SQL COUNT(DISTINCT) function.
The number of distinct values is an approximate number.
If the total number of rows before the distinct count feature is used is less than 10,000, the calculated result is close to the exact value.
If the total number of rows before the distinct count feature is used is greater than or equal to 100 million, the error rate is approximately 2%.
Parameters
Parameter
Description
name
The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName
The name of the field that is used to perform the aggregation operation. Only the LONG, DOUBLE, BOOLEAN, KEYWORD, GEO_POINT, and DATE types are supported.
missing
The default value for the field that is used to perform the aggregation operation on a row when the field value is empty.
If you do not specify a value for missing, the row is ignored.
If you specify a value for missing, the value of this parameter is used as the field value of the row.
Example
let searchQuery = { offset: 0, limit: 0, query: { queryType: TableStore.QueryType.MATCH_ALL_QUERY, }, getTotalCount: false, aggs: { aggs: [ { name: "AGG_DISTINCT_COUNT_test", type: TableStore.AggregationType.AGG_DISTINCT_COUNT, body: { fieldName: "col_long", missing: 666, }, }, ], }, }; let params = { tableName: tableName, indexName: indexName, searchQuery: searchQuery, columnToGet: { // Specify the columns that you want to return. You can set it to RETURN_SPECIFIED to return specified columns, RETURN_ALL to return all columns, RETURN_ALL_FROM_INDEX to return all columns in the search index, or RETURN_NONE to return only the primary key columns. returnType: TableStore.ColumnReturnType.RETURN_ALL_FROM_INDEX }, timeoutMs: 30000, } client.search(params, function (err, data) { if (err) { console.log('search error:', err.toString()); } else { console.log('search success:', data); } });
Percentile statistics
A percentile value indicates the relative position of a value in a dataset. For example, when you collect statistics for the response time of each request during the routine O&M of your system, you must analyze the response time distribution by using percentiles such as p25, p50, p90, and p99.
Parameters
Parameter
Description
name
The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName
The name of the field that is used to perform the aggregation operation. Only the LONG, DOUBLE, and DATE 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 that is used to perform the aggregation operation on a row when the field value is empty.
If you do not specify a value for missing, the row is ignored.
If you specify a value for missing, the value of this parameter is used as the field value of the row.
Example
let searchQuery = { offset: 0, limit: 0, query: { queryType: TableStore.QueryType.MATCH_ALL_QUERY, }, getTotalCount: false, aggs: { aggs: [ { name: "AGG_PERCENTILES_test", type: TableStore.AggregationType.AGG_PERCENTILES, body: { fieldName: "col_long", percentiles: [20, 50, 90, 100], missing: 888, }, }, ], }, }; let params = { tableName: tableName, indexName: indexName, searchQuery: searchQuery, columnToGet: { // Specify the columns that you want to return. You can set it to RETURN_SPECIFIED to return specified columns, RETURN_ALL to return all columns, RETURN_ALL_FROM_INDEX to return all columns in the search index, or RETURN_NONE to return only the primary key columns. returnType: TableStore.ColumnReturnType.RETURN_ALL_FROM_INDEX }, timeoutMs: 30000, } client.search(params, function (err, data) { if (err) { console.log('search error:', err.toString()); } else { console.log('search success:', data); } });
Group by field value
The aggregation method that can be used to group query results based on field values. The values that are the same are grouped together. The identical value of each group and the number of identical values in each group are returned.
The calculated number may be different from the actual number if the number of values in a group is very large.
Parameters
Parameter
Description
name
The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName
The name of the field that is used to perform the aggregation operation. Only the LONG, DOUBLE, BOOLEAN, KEYWORD, and DATE types are supported.
sort
The sorting rules for groups. By default, groups are sorted based on the number of items in the groups in descending order. If you configure multiple sorting rules, the groups are sorted based on the order in which the rules are configured. Supported sorting rules:
Sort by value in alphabetical order.
Sort by value in reverse alphabetical order.
Sort by row count in ascending order.
Sort by row count in descending order.
Sort by the values that are obtained from sub-aggregation results in ascending order.
Sort by the values that are obtained from sub-aggregation results in descending order.
size
The number of groups that you want to return. Default value: 10. Maximum value: 2000. If the number of groups exceeds 2,000, only the first 2,000 groups are returned.
subAggs and subGroupBys
The sub-aggregation operation. You can perform the sub-aggregation operation 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, perform two sub-aggregation operations to obtain the maximum and minimum product prices in each category.
Example:
Fruits: 5. The maximum price is CNY 15. The minimum price is CNY 3.
Toiletries: 10. The maximum price is CNY 98. The minimum price is CNY 1.
Electronic devices: 3. The maximum price is CNY 8,699. The minimum price is CNY 2,300.
Other products: 15. The maximum price is CNY 1,000. The minimum price is CNY 80.
Example
let searchQuery = { offset: 0, limit: 0, query: { queryType: TableStore.QueryType.MATCH_ALL_QUERY, }, getTotalCount: false, groupBys: { groupBys: [ { name: "group_by_GROUP_BY_FIELD", type: TableStore.GroupByType.GROUP_BY_FIELD, body: { fieldName: "city", size: 111, sort: { sorters: [ { groupKeySort: { order: TableStore.SortOrder.SORT_ORDER_ASC, }, }, { rowCountSort: { order: TableStore.SortOrder.SORT_ORDER_DESC, }, }, ], }, subGroupBys: { // Nested subGroupBys. groupBys: [ { name: "group_by_GROUP_BY_RANGE", type: TableStore.GroupByType.GROUP_BY_RANGE, body: { fieldName: "age", ranges: [ { from: 4, to: 5, }, { from: 6, to: 7, }, ], subAggs: { // Nested sub-aggregations. aggs: [ { name: "AGG_COUNT_test", type: TableStore.AggregationType.AGG_COUNT, body: { fieldName: "*", missing: 8, }, }, ], }, }, }, ], }, }, }, ], }, }; let params = { tableName: tableName, indexName: indexName, searchQuery: searchQuery, columnToGet: { // Specify the columns that you want to return. You can set it to RETURN_SPECIFIED to return specified columns, RETURN_ALL to return all columns, RETURN_ALL_FROM_INDEX to return all columns in the search index, or RETURN_NONE to return only the primary key columns. returnType: TableStore.ColumnReturnType.RETURN_ALL_FROM_INDEX }, timeoutMs: 30000, } client.search(params, function (err, data) { if (err) { console.log('search error:', err.toString()); } else { console.log('search success:', data); } });
Group by range
The aggregation method that can be used to group query results based on the value ranges of a field. Field values that are within a specified range are grouped together. The number of values in each range is returned.
Parameters
Parameter
Description
name
The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName
The name of the field that is used to perform the aggregation operation. Only the LONG and DOUBLE types are supported.
ranges[from, to)
The value ranges that are used for grouping.
The value range can start from Double.MIN_VALUE and end with Double.MAX_VALUE.
subAggs and subGroupBys
The sub-aggregation operation. You can perform the sub-aggregation operation based on the grouping results.
For example, after you group query results by sales volume and by province, you can obtain the province that has the largest proportion of sales volume in a specified range. You must specify a value for GroupByField in GroupByRange to perform this query.
Example
let searchQuery = { offset: 0, limit: 0, query: { queryType: TableStore.QueryType.MATCH_ALL_QUERY, }, getTotalCount: false, groupBys: { groupBys: [ { name: "group_by_GROUP_BY_RANGE", type: TableStore.GroupByType.GROUP_BY_RANGE, body: { fieldName: "col_long", ranges: [ { from: 1, to: 5, }, { from: 3, to: 20, }, ], }, }, ], }, }; let params = { tableName: tableName, indexName: indexName, searchQuery: searchQuery, columnToGet: { // Specify the columns that you want to return. You can set it to RETURN_SPECIFIED to return specified columns, RETURN_ALL to return all columns, RETURN_ALL_FROM_INDEX to return all columns in the search index, or RETURN_NONE to return only the primary key columns. returnType: TableStore.ColumnReturnType.RETURN_ALL_FROM_INDEX }, timeoutMs: 30000, } client.search(params, function (err, data) { if (err) { console.log('search error:', err.toString()); } else { console.log('search success:', data); } });
Group by geographical location
The aggregation method that can be used to group query results based on geographical locations to a central point. Query results in distances that are within a specified range are grouped together. The number of items in each range is returned.
Parameters
Parameter
Description
name
The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName
The name of the field that is used for the aggregation operation. Only the GEOPOINT type is supported.
origin(lat, lon)
The longitude and latitude of the central point.
lat specifies the latitude of the central point. lon specifies the longitude of the central point.
ranges[from, to)
The distance ranges that are used for grouping. Unit: meters.
The value range can start from Double.MIN_VALUE and end with Double.MAX_VALUE.
subAggs and subGroupBys
The sub-aggregation operation. You can perform the sub-aggregation operation based on the grouping results.
Example
let searchQuery = { offset: 0, limit: 0, query: { queryType: TableStore.QueryType.MATCH_ALL_QUERY, }, getTotalCount: false, groupBys: { groupBys: [ { name: "group_by_GROUP_BY_GEO_DISTANCE", type: TableStore.GroupByType.GROUP_BY_GEO_DISTANCE, body: { fieldName: "col_geo", origin: { lat: 50, lon: 60, }, ranges: [ { from: 1, to: 2, }, { from: 3, }, ], }, }, ], }, }; let params = { tableName: tableName, indexName: indexName, searchQuery: searchQuery, columnToGet: { // Specify the columns that you want to return. You can set it to RETURN_SPECIFIED to return specified columns, RETURN_ALL to return all columns, RETURN_ALL_FROM_INDEX to return all columns in the search index, or RETURN_NONE to return only the primary key columns. returnType: TableStore.ColumnReturnType.RETURN_ALL_FROM_INDEX }, timeoutMs: 30000, } client.search(params, function (err, data) { if (err) { console.log('search error:', err.toString()); } else { console.log('search success:', data); } });
Group by filter
The aggregation method that can be used to filter the query results and group them together to obtain the number of results that match each filter. Results are returned in the order in which the filters are specified.
Parameters
Parameter
Description
name
The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
filters
The filters that can be used for the query. Results are returned in the order in which the filters are specified.
subAggs and subGroupBys
The sub-aggregation operation. You can perform the sub-aggregation operation based on the grouping results.
Example
let searchQuery = { offset: 0, limit: 0, query: { queryType: TableStore.QueryType.MATCH_ALL_QUERY, }, getTotalCount: false, groupBys: { groupBys: [ { name: "group_by_GROUP_BY_FILTER", type: TableStore.GroupByType.GROUP_BY_FILTER, body: { filters: [ { queryType: TableStore.QueryType.MATCH_ALL_QUERY, }, { queryType: TableStore.QueryType.WILDCARD_QUERY, query: { fieldName: "col_keyword", value: "1*" }, }, ], }, }, ], }, }; let params = { tableName: tableName, indexName: indexName, searchQuery: searchQuery, columnToGet: { // Specify the columns that you want to return. You can set it to RETURN_SPECIFIED to return specified columns, RETURN_ALL to return all columns, RETURN_ALL_FROM_INDEX to return all columns in the search index, or RETURN_NONE to return only the primary key columns. returnType: TableStore.ColumnReturnType.RETURN_ALL_FROM_INDEX }, timeoutMs: 30000, } client.search(params, function (err, data) { if (err) { console.log('search error:', err.toString()); } else { console.log('search success:', data); } });
Query by histogram
The aggregation method that can be used to group query results based on specific data intervals. Field values that are within the same range are grouped together. The value range of each group and the number of values in each group are returned.
Parameters
Parameter
Description
name
The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
fieldName
The name of the field that is used to perform the aggregation operation. Only the LONG and DOUBLE types are supported.
interval
The data interval that is used to obtain aggregation results.
fieldRange[min,max]
The range that is used together with the interval parameter to limit the number of groups.
The value that is calculated by using the formula cannot exceed 2,000.minDocCount
The minimum number of rows. If the number of rows in a group is less than the minimum number of rows, the aggregation results for the group are not returned.
missing
The default value for the field that is used to perform the aggregation operation on a row when the field value is empty.
If you do not specify a value for missing, the row is ignored.
If you specify a value for missing, the value of this parameter is used as the field value of the row.
Example
let searchQuery = { offset: 0, limit: 0, query: { queryType: TableStore.QueryType.MATCH_ALL_QUERY, }, getTotalCount: false, groupBys: { groupBys: [ { name: "group_by_GROUP_BY_HISTOGRAM", type: TableStore.GroupByType.GROUP_BY_HISTOGRAM, body: { fieldName: "col_long", interval: Long.fromNumber(3), missing: Long.fromNumber(123), minDocCount: 5, fieldRange: { min: Long.fromNumber(1), max: Long.fromNumber(999), }, sort: { sorters: [ { groupKeySort: { order: TableStore.SortOrder.SORT_ORDER_ASC, }, }, { rowCountSort: { order: TableStore.SortOrder.SORT_ORDER_ASC, }, }, ], }, }, }, ], }, }; let params = { tableName: tableName, indexName: indexName, searchQuery: searchQuery, columnToGet: { // Specify the columns that you want to return. You can set it to RETURN_SPECIFIED to return specified columns, RETURN_ALL to return all columns, RETURN_ALL_FROM_INDEX to return all columns in the search index, or RETURN_NONE to return only the primary key columns. returnType: TableStore.ColumnReturnType.RETURN_ALL_FROM_INDEX }, timeoutMs: 30000, } client.search(params, function (err, data) { if (err) { console.log('search error:', err.toString()); } else { console.log('search success:', data); } });
Query the rows that are obtained from the results of an aggregation operation in each group
After you group query results, you can query the rows in each group. This method can be used in a similar manner as ANY_VALUE(field) in MySQL.
Parameters
Parameter
Description
name
The unique name of the aggregation operation. You can query the results of a specific aggregation operation based on this name.
limit
The maximum number of rows that can be returned for each group. By default, only one row of data is returned.
sort
The sorting method that is used to sort data in groups.
columnsToGet
The fields that you want to return. Only fields in search indexes are supported. ARRAY, DATE, GEOPOINT, and NESTED fields are not supported.
The value of this parameter is the same as the value of columnsToGet in SearchRequest. You need to only specify columnsToGet in SearchRequest.
Example
An activity application form of a school contains fields in which information such as the names of students, classes, head teachers, and class presidents can be specified. You can group students by class to view the application statistics and the property information of each class. The equivalent SQL statement is
select className, ANY_VALUE(teacher), ANY_VALUE(monitor), COUNT(*) as number from table GROUP BY className.let searchQuery = { offset: 0, limit: 0, query: { queryType: TableStore.QueryType.MATCH_ALL_QUERY, }, getTotalCount: true, groupBys: { groupBys: [ { name: "group_by_name_xxx", type: TableStore.GroupByType.GROUP_BY_FIELD, body: { fieldName: "className", size: 200, subAggs: { aggs: [ { name: "top_row_name_xxx", type: TableStore.AggregationType.AGG_TOP_ROWS, body: { limit: 1, sort: { sorters: [ { fieldSort: { fieldName: "teacher", order: TableStore.SortOrder.SORT_ORDER_DESC, }, }, ], }, }, }, ], }, }, }, ], }, }; let params = { tableName: tableName, indexName: indexName, searchQuery: searchQuery, columnToGet: { // Specify the columns that you want to return. You can set it to RETURN_SPECIFIED to return specified columns, RETURN_ALL to return all columns, RETURN_ALL_FROM_INDEX to return all columns in the search index, or RETURN_NONE to return only the primary key columns. returnType: TableStore.ColumnReturnType.RETURN_ALL_FROM_INDEX }, timeoutMs: 30000, } client.search(params, function (err, data) { if (err) { console.log('search error:', err.toString()); } else { console.log('search success:', data); } });