Collapse query results - Tablestore SDK for Python - Tablestore

Prerequisites

Install the Tablestore SDK for Python and initialize a client.

Description

Collapse groups matching rows by a specified search index field and returns one representative row for each distinct field value. The query sort order determines which row is returned from each group. For more information, see Sort and paginate results. Collapse changes only query results and does not modify the table.

Important

The collapse field must have sorting and aggregation enabled and must be a non-array Keyword, Long, or Double field. Collapse queries support pagination only by using limit and offset, not next_token. limit + offset cannot exceed 100000. Aggregations, grouping, and total matching row count are based on results before collapse. The response does not provide the total number of collapsed groups.

The following example collapses results by category and sorts by price in descending order. Therefore, the row with the highest price is returned for each category.

query = MatchAllQuery()
search_query = SearchQuery(
    query,
    sort=Sort([FieldSort("price", SortOrder.DESC)]),
    collapse_field=Collapse("category"),
    limit=10,
    get_total_count=True,
)
response = client.search(
    "example_table",
    "example_index",
    search_query,
    ColumnsToGet(
        ["category", "price"],
        ColumnReturnType.SPECIFIED,
    ),
)
print(response.total_count)
print(response.rows)

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 parameters.

Name	Type	Description
query (required)	`Query`	The query condition. Specify a Query type based on your matching requirements.
sort (optional)	`Sort`	The sort order of results. For more information, see Sort and paginate results.
get_total_count (optional)	`bool`	Specifies whether to return the total number of matching rows. Default value: `False`. Setting this parameter to `True` increases query overhead.
next_token (optional)	`bytes`	Do not specify this parameter for a collapse query.
offset (optional)	`int`	The starting group position. Default value: `0`. The sum of this parameter and `limit` cannot exceed `100000`.
limit (optional)	`int`	The maximum number of rows to return. If this parameter is set to `0`, no rows are returned.
aggs (optional)	`list[Agg]`	The metric aggregation configurations. For more information, see Aggregation.
group_bys (optional)	`list[BaseGroupBy]`	The grouping configurations. For more information, see Aggregation.
collapse_field (required)	`Collapse`	The result collapse configuration.
highlight (optional)	`Highlight`	The summary and highlighting configuration for `Text` fields. For more information, see Summary and highlighting.

Collapse configuration

search_query.collapse_field is of the Collapse type and contains the following parameter.

Name	Type	Description
field_name (required)	`str`	The collapse field name. Sorting and aggregation must be enabled for this non-array `Keyword`, `Long`, or `Double` field.

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 after collapse. At most one row is returned for each distinct collapse field value.
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 before collapse. The value depends on `get_total_count` and is not the number of collapsed groups.
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.

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