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

Use Tablestore SDK for Python to perform metric aggregations and grouping on search index results.

Prerequisites

Install the Tablestore SDK for Python and initialize a client.

The aggregation feature requires SDK version 5.2.1 or later. We recommend that you use the latest SDK version.

Description

Aggregation calculates metrics or creates groups from search index query results. Add metric aggregation objects to SearchQuery.aggs and grouping objects to SearchQuery.group_bys. Each name in a request must be unique and identifies the corresponding response result. To retrieve only aggregation results, set limit to 0.

Feature	Description
Min, Max, Sum, Avg	Calculate the minimum, maximum, sum, and average.
Count, DistinctCount	Count rows with non-empty field values and distinct field values.
Percentiles	Calculate one or more percentiles.
TopRows	Return top-sorted rows in each group as a sub-aggregation.
GroupByField, GroupByComposite	Group by one field value or a combination of fields.
GroupByRange, GroupByGeoDistance, GroupByFilter	Group by numeric range, geo distance, or filter conditions.
GroupByHistogram, GroupByDateHistogram, GroupByGeoGrid	Create numeric histograms, date histograms, or GeoHash grid groups.

Important

Search index fields used for aggregation must have sorting and aggregation enabled. Supported field types vary by aggregation and grouping type. You can configure up to five groupings at the same level. Distinct count, percentiles, and field grouping use approximate calculation. A large number of aggregations or deeply nested aggregations increases request complexity and latency.

The following example calculates the minimum, maximum, and average of price and groups rows by category.

search_query = SearchQuery(
    MatchAllQuery(),
    limit=0,
    aggs=[
        Min("price", name="min_price"),
        Max("price", name="max_price"),
        Avg("price", name="avg_price"),
    ],
    group_bys=[
        GroupByField("category", name="by_category"),
    ],
)
response = client.search(
    "example_table",
    "example_index",
    search_query,
)
for result in response.agg_results:
    print(result.name, result.value)
for result in response.group_by_results:
    print(result.name, result.items)

Parameters

Search request

The search method contains the following parameters.

Name	Type	Description
table_name (required)	`str`	The name of the data table.
index_name (required)	`str`	The name of the search index.
search_query (required)	`SearchQuery`	The query condition and common query configurations.
columns_to_get (optional)	`ColumnsToGet`	The return column configuration. If this parameter is not specified, only primary key columns are returned.
routing_keys (optional)	`list`	The primary key values of custom routing fields. This parameter is not required if custom routing is not configured.
timeout_s (optional)	`int`	The request timeout in seconds. If this parameter is not specified, the client-level timeout is used.

Query configuration

search_query is of the SearchQuery type and contains the following aggregation-related parameters.

Name	Type	Description
query (required)	`Query`	The query condition that determines the aggregation scope. Use `MatchAllQuery` to aggregate all rows.
aggs (optional)	`list[Agg]`	The metric aggregation list. You can combine aggregations that have different names.
group_bys (optional)	`list[BaseGroupBy]`	The grouping list. You can combine groupings that have different names.
limit (optional)	`int`	The number of query rows to return. Set this parameter to `0` to retrieve only aggregation results.

Metric aggregations

Add the following objects to search_query.aggs. field is the aggregation field, name identifies the result, and missing_value is used when the field is missing. If missing_value is not specified, rows that lack the field are ignored.

Min, Max, and Avg

Name	Type	Description
field (required)	`str`	The aggregation field. Supported types: `Long`, `Double`, and `Date`.
missing_value (optional)	`str / int / float`	The value used when the field is missing.
name (optional)	`str`	The aggregation name. Defaults: `min`, `max`, and `avg`, respectively.

Sum

Name	Type	Description
field (required)	`str`	The aggregation field. Supported types: `Long` and `Double`.
missing_value (optional)	`int / float`	The value used in the sum when the field is missing.
name (optional)	`str`	The aggregation name. Default value: `sum`.

Count

Name	Type	Description
field (required)	`str`	The field whose non-empty rows are counted. Supported types: `Long`, `Double`, `Boolean`, `Keyword`, `Date`, and `GeoPoint`.
name (optional)	`str`	The aggregation name. Default value: `count`.

DistinctCount

Name	Type	Description
field (required)	`str`	The field whose distinct values are counted. Supported types: `Long`, `Double`, `Boolean`, `Keyword`, `Date`, and `GeoPoint`.
missing_value (optional)	`str / int / float / bool`	The value included in the distinct count when the field is missing.
name (optional)	`str`	The aggregation name. Default value: `distinct_count`.

Percentiles

Name	Type	Description
field (required)	`str`	The aggregation field. Supported types: `Long`, `Double`, and `Date`.
percentiles_list (required)	`list[float]`	The percentiles to calculate, such as `[50, 90, 99]`.
missing_value (optional)	`str / int / float`	The value used when the field is missing.
name (optional)	`str`	The aggregation name. Default value: `percentiles`.

TopRows

Name	Type	Description
limit (required)	`int`	The maximum number of rows to return from each group.
sort (required)	`Sort`	The sort order of rows in each group.
name (optional)	`str`	The aggregation name. Default value: `top_rows`.

Note

DistinctCount is approximate. Results are close to exact below 10,000 distinct values, and the error is about 2% at 100 million values. Percentiles is also approximate, and extreme percentiles are typically more accurate than the median. Use TopRows only as a sub-aggregation of a grouping.

Grouping

Add the following objects to search_query.group_bys. Use sub_aggs and sub_group_bys to perform metric sub-aggregations and subgrouping in each group.

GroupByField

Name	Type	Description
field_name (required)	`str`	The grouping field. Supported types: `Long`, `Double`, `Boolean`, `Keyword`, and `Date`.
size (optional)	`int`	The number of groups to return. Default value: `10`. Maximum value: `2000`.
group_by_sort (optional)	`list`	The group sort rules. By default, groups are sorted by row count in descending order. `GroupKeySort`, `RowCountSort`, and `SubAggSort` are supported.
sub_aggs (optional)	`list[Agg]`	The metric sub-aggregations.
sub_group_bys (optional)	`list[BaseGroupBy]`	The subgroupings.
name (optional)	`str`	The grouping name. Default value: `group_by_field`.

GroupByComposite

Name	Type	Description
sources (required)	`list[BaseGroupBy]`	The multi-field grouping sources. You can specify up to 32 sources of the `GroupByField`, `GroupByHistogram`, or `GroupByDateHistogram` type.
size (optional)	`int`	The number of groups to return. Default value: `10`. Maximum value: `2000`.
next_token (optional)	`bytes`	The token for the next page of groups. Omit it in the first request.
suggested_size (optional)	`int`	A soft limit for high-throughput compute scenarios. Do not specify it together with `size`.
sub_aggs (optional)	`list[Agg]`	The metric sub-aggregations.
sub_group_bys (optional)	`list[BaseGroupBy]`	The subgroupings. `GroupByComposite` cannot be a subgroup of another grouping.
name (optional)	`str`	The grouping name. Default value: `group_by_composite`.

GroupByRange

Name	Type	Description
field_name (required)	`str`	The grouping field. Supported types: `Long` and `Double`.
ranges (required)	`list[tuple]`	The left-closed, right-open ranges, such as `[(0, 100), (100, 200)]`.
sub_aggs (optional)	`list[Agg]`	The metric sub-aggregations.
sub_group_bys (optional)	`list[BaseGroupBy]`	The subgroupings.
name (optional)	`str`	The grouping name. Default value: `group_by_range`.

GroupByGeoDistance

Name	Type	Description
field_name (required)	`str`	The `GeoPoint` grouping field.
origin (required)	`GeoPoint`	The center point. Constructor parameters are latitude followed by longitude.
ranges (required)	`list[tuple]`	The distance ranges in meters. Each range is left-closed and right-open.
sub_aggs (optional)	`list[Agg]`	The metric sub-aggregations.
sub_group_bys (optional)	`list[BaseGroupBy]`	The subgroupings.
name (optional)	`str`	The grouping name. Default value: `group_by_geo_distance`.

GroupByFilter

Name	Type	Description
filters (required)	`list[Query]`	The filter conditions. Result order matches condition order.
sub_aggs (optional)	`list[Agg]`	The metric sub-aggregations.
sub_group_bys (optional)	`list[BaseGroupBy]`	The subgroupings.
name (optional)	`str`	The grouping name. Default value: `group_by_filter`.

GroupByHistogram

Name	Type	Description
field_name (required)	`str`	The grouping field. Supported types: `Long` and `Double`.
interval (required)	`int / float`	The numeric histogram interval.
field_range (required)	`FieldRange`	The aggregation range. `(max-min)/interval` cannot exceed `2000`.
missing_value (optional)	`int / float`	The value used in the histogram when the field is missing.
min_doc_count (optional)	`int`	The minimum row count for a bucket. Buckets with fewer rows are not returned.
group_by_sort (optional)	`list`	The group sort rules.
sub_aggs (optional)	`list[Agg]`	The metric sub-aggregations.
sub_group_bys (optional)	`list[BaseGroupBy]`	The subgroupings.
name (optional)	`str`	The grouping name. Default value: `group_by_histogram`.

GroupByDateHistogram

Name	Type	Description
field_name (required)	`str`	The `Date` grouping field.
interval (required)	`DateTimeValue`	The date or time interval, composed of a value and `DateTimeUnit`.
field_range (required)	`FieldRange`	The aggregation range. The server requires this parameter even though the Python constructor allows it to be omitted.
missing (optional)	`str`	The date value used in the histogram when the field is missing.
min_doc_count (optional)	`int`	The minimum row count for a bucket. Buckets with fewer rows are not returned.
time_zone (optional)	`str`	The time zone in `+hh:mm` or `-hh:mm` format, such as `+08:00`.
group_by_sort (optional)	`list`	The group sort rules.
offset (optional)	`DateTimeValue`	The bucket-boundary offset from the default origin.
sub_aggs (optional)	`list[Agg]`	The metric sub-aggregations.
sub_group_bys (optional)	`list[BaseGroupBy]`	The subgroupings.
name (optional)	`str`	The grouping name. Default value: `group_by_date_histogram`.

GroupByGeoGrid

Name	Type	Description
field_name (required)	`str`	The `GeoPoint` grouping field.
precision (required)	`GeoHashPrecision`	The GeoHash grid precision. A higher enum number represents a smaller grid.
size (optional)	`int`	The number of grid groups to return.
sub_aggs (optional)	`list[Agg]`	The metric sub-aggregations.
sub_group_bys (optional)	`list[BaseGroupBy]`	The subgroupings.
name (optional)	`str`	The grouping name. Default value: `group_by_geo_grid`.

Note

GroupByComposite requires Tablestore SDK for Python 6.4.4 or later. If many groups exist, specify size and paginate by using next_token from each result until the token is empty.

Return columns

columns_to_get is of the ColumnsToGet type and contains the following parameters.

Name	Type	Description
column_names (optional)	`list[str]`	The names of attribute columns to return. Specify this parameter only when `return_type` is `SPECIFIED`.
return_type (optional)	`ColumnReturnType`	The return column mode. `NONE` (default) returns only primary key columns; `SPECIFIED` returns specified attribute columns; `ALL` returns all attribute columns in the table; and `ALL_FROM_INDEX` returns all stored fields in the index.

Response

The search method returns SearchResponse. The following table describes the core fields.

Field	Type	Description
rows	`list[Row]`	The rows returned by the query. The number does not exceed `limit`.
next_token	`bytes`	The token for the next page. An empty value indicates that no more data is available.
total_count	`int`	The number of matching rows. The value depends on `get_total_count`.
is_all_succeed	`bool`	Indicates whether all index partitions were queried. If the value is `False`, partial results are returned.
agg_results	`list[AggResult]`	The metric aggregation results. This field is empty if `aggs` is not configured.
group_by_results	`list[GroupByResult]`	The grouping results. This field is empty if `group_bys` is not configured.
search_hits	`list[SearchHit]`	The search hits, including extended information such as rows, relevance scores, and highlights.

Each item in agg_results uses name and value to identify an aggregation and its value. Each item in group_by_results uses name and items to return group keys, row counts, sub-aggregations, and subgroupings. A GroupByComposite result also contains source_group_by_names and next_token for continued reads. The keys of each group is a string list aligned with sources; a missing field value is represented by None.

Tuple-compatible response

Starting from Tablestore SDK for Python 5.2.0, search APIs return response objects instead of tuples. Version 5.1.0 and earlier return tuples directly. In version 5.2.1 and later, you can call SearchResponse.v1_response() to obtain a tuple compatible with earlier versions. For new code, access SearchResponse attributes directly to avoid unpacking errors if response fields are extended.

(
    rows,
    next_token,
    total_count,
    is_all_succeed,
    agg_results,
    group_by_results,
    search_hits,
) = response.v1_response()

Examples

Paginate a multi-field grouping

The following example groups by category and price and returns two groups per page.

sources = [
    GroupByField("category", name="category_source"),
    GroupByField("price", name="price_source"),
]
next_token = None
all_items = []

while True:
    group_by = GroupByComposite(
        sources,
        size=2,
        next_token=next_token,
        name="by_category_and_price",
    )
    response = client.search(
        "example_table",
        "example_index",
        SearchQuery(MatchAllQuery(), limit=0, group_bys=[group_by]),
    )
    result = response.group_by_results[0]
    all_items.extend(result.items)
    next_token = result.next_token
    if not next_token:
        break

for item in all_items:
    print(item.keys, item.row_count)

Create a date histogram and geo grid

The following example creates a daily date histogram and groups locations into GeoHash grids of approximately 39 km by 19 km.

group_bys = [
    GroupByDateHistogram(
        "event_date",
        DateTimeValue(1, DateTimeUnit.DAY),
        field_range=FieldRange("2026-08-01", "2026-08-04"),
        name="by_day",
    ),
    GroupByGeoGrid(
        "location",
        GeoHashPrecision.GHP_39KM_19KM_4,
        name="by_geo_grid",
    ),
]
response = client.search(
    "example_table",
    "example_index",
    SearchQuery(MatchAllQuery(), limit=0, group_bys=group_bys),
)
for result in response.group_by_results:
    print(result.name, result.items)