How to use collapse to remove duplicates in a search index-Tablestore(Tablestore)-阿里云帮助中心

If a query returns many results that share a common value in a specific column, you can use the collapse feature. This feature groups the result set by that column and ensures that each unique value appears only once in the results. This helps you view a more diverse set of data.

In most scenarios, the `collapse` feature can be used to remove duplicates, similar to a `DISTINCT` operation. It removes duplicates based on the values in the specified column. This feature supports only integer, floating-point number, and Keyword type columns. It does not support array type columns. The feature returns only the first 100,000 sorted results.

Notes

The `collapse` feature supports pagination only with `offset` and `limit`. It does not support token-based pagination.
When you use statistical aggregation and `collapse` on a result set, the aggregation is applied to the result set before it is collapsed.
After you use the `collapse` feature, the total number of groups returned is limited by the `offset` and `limit` parameters. The feature can return a maximum of 100,000 groups.
The total row count returned in the results is the number of matching rows before the `collapse` operation. You cannot retrieve the total number of groups after the collapse.

API

The `collapse` (distinct) feature is implemented using the collapse parameter in the Search API operation.

Parameters

Parameter	Description
query	Any query type.
collapse	The collapse settings. Includes the `fieldName` parameter. `fieldName`: The name of the column to collapse the result set on. This feature supports only integer, floating-point number, and Keyword type columns. It does not support array type columns.
offset	The starting position of the query.
limit	The maximum number of results to return. To get only the row count without any data, set `limit` to 0. This setting returns no rows.
getTotalCount	Specifies whether to return the total number of matching rows. The default value is `false`. Returning the total row count can affect query performance.
tableName	The name of the data table.
indexName	The name of the search index.
columnsToGet	Specifies which columns to return. Includes the `returnAll` and `columns` settings. By default, `returnAll` is `false`. If `returnAll` is `false`, you can use `columns` to specify which columns to return. If you do not specify any columns, only the primary key columns are returned. If you set `returnAll` to `true`, all columns are returned.

Usage

You can use the command-line interface or a software development kit (SDK) to collapse (remove duplicates from) data during a query. Before you begin, complete the following preparations.

You have an Alibaba Cloud account or a RAM user that has permissions to perform operations on Tablestore. For information about how to grant Tablestore operation permissions to a RAM user, see Use a RAM policy to grant permissions to a RAM user.
If you want to use Tablestore SDKs or the Tablestore CLI to perform a query, an AccessKey pair is created for your Alibaba Cloud account or RAM user. For more information, see Create an AccessKey pair.
A data table is created. For more information, see Operations on tables.
A search index is created for the data table. For more information, see Create a search index.
If you want to use Tablestore SDKs to perform a query, an OTSClient instance is initialized. For more information, see Initialize a Tablestore client.
If you want to use the Tablestore CLI to perform a query, the Tablestore CLI is downloaded and started, and information about the instance that you want to access and the data table are configured. For more information, see Download the Tablestore CLI, Start the Tablestore CLI and configure access information, and Manage data tables.

Use the command-line interface

You can run the search command in the command line interface to query data from a search index. To enable the collapse (distinct) feature, set the `collapse` parameter in the query. For more information, see Search Index.

Run the `search` command to query data from the table using the `search_index` search index. This command returns all indexed columns.
```
search -n search_index --return_all_indexed
```

When prompted, enter the query conditions. The following code provides an example:

{
    "Offset": -1,
    "Limit": 10,
    "Collapse": {
        "FieldName": "product_name"
    },
    "Sort": null,
    "GetTotalCount": true,
    "Token": null,
    "Query": {
        "Name": "MatchQuery",
        "Query": {
            "FieldName": "user_id",
            "Text": "00002",
            "MinimumShouldMatch": 1
        }
    }
}

Use an SDK

You can use Java SDK, Go SDK, Python SDK, Node.js SDK, .NET SDK, and PHP SDK to use the collapse (distinct) feature when you query data. The following example uses the Java SDK to show how to use the collapse (distinct) feature.

The following sample code provides an example on how to query the rows in which the value of the user_id column matches "00002" and then collapse the result set based on the value of the product_name column:

private static void UseCollapse(SyncClient client){
    SearchQuery searchQuery = new SearchQuery(); // Specify the query conditions. 
    MatchQuery matchQuery = new MatchQuery();
    matchQuery.setFieldName("user_id");
    matchQuery.setText("00002");

    searchQuery.setQuery(matchQuery);
    Collapse collapse = new Collapse("product_name"); // Collapse the result set based on the product_name column. 
    searchQuery.setCollapse(collapse);

    //searchQuery.setOffset(1000);// The position from which the current query starts. 
    searchQuery.setLimit(20);
    //searchQuery.setGetTotalCount(true);// Set the GetTotalCount parameter to true to return the total number of rows that meet the query conditions. 

    SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);// Specify the name of the data table and the name of the search index.     
    // You can use the columnsToGet parameter to specify the columns that you want to return or specify that all columns are returned. If you do not specify this parameter, only the primary key columns are returned. 
    //SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
    //columnsToGet.setReturnAll(true); // Set the ReturnAll parameter to true to return all columns. 
    //columnsToGet.setColumns(Arrays.asList("ColName1","ColName2")); // Specify the columns that you want to return. 
    //searchRequest.setColumnsToGet(columnsToGet);

    SearchResponse response = client.search(searchRequest);  
    //System.out.println(response.getTotalCount());    
    //System.out.println(response.getRows().size()); // Display the number of rows that are returned based on the product_name column. 
    System.out.println(response.getRows()); // Display the product names that are returned based on the product_name column. 
}

Billing

In VCU mode (formerly Reserved mode), querying data using a search index consumes VCU compute resources. In CU mode (formerly Pay-As-You-Go mode), querying data using a search index consumes read throughput. For more information, see Search Index Metering and Billing.

Using the collapse (remove duplicates) feature does not change the existing billing rules.

FAQ

References

When you use a search index to query data, you can use the following query methods: term query, terms query, match all query, match query, match phrase query, prefix query, range query, wildcard query, fuzzy query, Boolean query, geo query, nested query, KNN vector query, and exists query. You can select query methods based on your business requirements to query data from multiple dimensions.
You can sort or paginate rows that meet the query conditions by using the sorting and paging features. For more information, see Perform sorting and paging.
You can use the collapse (distinct) feature to collapse the result set based on a specific column. This way, data of the specified type appears only once in the query results. For more information, see Collapse (remove duplicates).
If you want to analyze data in a data table, you can use the aggregation feature of the Search operation or execute SQL statements. For example, you can obtain the minimum and maximum values, sum, and total number of rows. For more information, see Aggregation and SQL query.
If you want to obtain all rows that meet the query conditions without the need to sort the rows, you can call the ParallelScan and ComputeSplits operations to use the parallel scan feature. For more information, see Parallel scan.