Implement Suffix Search on FuzzyKeyword Fields - Tablestore

Search for rows where a field value ends with a specific string. For example, find users by the last four digits of a phone number, or locate resources by a trailing identifier.

How it works

Suffix query works only on FuzzyKeyword fields. FuzzyKeyword is a data type that is optimized for features, such as suffix query, prefix query, and wildcard query. Query performance remains stable regardless of data volume.

To run a suffix query, set the field type to FuzzyKeyword when you create or update the search index. Suffix query does not work on Keyword fields.

Limitations

Limitation	Workaround
Sorting and aggregation are not supported on FuzzyKeyword fields.	Create a virtual column mapped to the Keyword type for sorting or aggregation.
Suffix matching is not directly supported on Keyword fields.	Reverse the field value at write time and use a prefix query instead.

Parameters

Parameter	Description
`query`	The query type. Set to `SuffixQuery`.
`fieldName`	The name of the field to match.
`suffix`	The suffix string to match against field values.
`tableName`	The name of the data table.
`indexName`	The name of the search index.
`getTotalCount`	Whether to return the total number of matching rows. The default value is `false`. Setting this to `true` increases query latency.
`weight`	A positive floating-point number that adjusts the BM25 relevance score for this field. A higher weight increases the field's score contribution. Does not affect the number of returned rows.
`columnsToGet`	Controls which columns to return. You can configure the `returnAll` and `columns` fields. The default value of `returnAll` is `false`. If you do not specify the columns to return, only the primary key columns are returned. Set `returnAll` to `true` to return all columns.

API operations

Call the Search or ParallelScan operation with the query type set to SuffixQuery.

Prerequisites

Before you begin, make sure that you have:

A Resource Access Management (RAM) user with Tablestore permissions. For details, see Use the AccessKey pair of a RAM user to access Tablestore.
A data table. For details, see Operations on data tables.
A search index with at least one FuzzyKeyword field. For details, see Create a search index.

Use the Tablestore console

Go to the Indexes tab.
1. Log on to the Tablestore console.
2. In the top navigation bar, select a resource group and a region.
3. On the Overview page, click the name of the instance that you want to manage or click Manage Instance in the Actions column.
4. On the Tables tab of the Instance Details tab, click the name of the data table or click Indexes in the Actions column.
Find the search index and click Manage Data in the Actions column.
1. By default, all attribute columns are returned. To return specific attribute columns, turn off All Columns and enter the column names separated by commas (,). > Note: All primary key columns of the data table are always returned.
2. Select the And, Or, or Not logical operator.
  - And: returns rows that match all query conditions.
  - Or: returns rows that match at least one query condition.
  - Not: returns rows that do not match the query conditions.
3. Select a FuzzyKeyword field and click Add.
4. From the Query Type drop-down list, select SuffixQuery(SuffixQuery) and enter the suffix value.
5. (Optional) Turn on Sort to sort results by specific fields.
6. (Optional) Turn on Collect Statistics to aggregate data on specific fields.
Click OK. Matching rows are displayed on the Indexes tab.

Use Tablestore SDKs

You can use Tablestore SDK for Java to perform a suffix query.

Important

Tablestore SDK for Java V5.17.0 or later supports suffix query. Initialize a Tablestore client before running the query. For details, see Initialize a Tablestore client.

The following example queries rows where the Col_FuzzyKeyword column ends with "hangzhou".

/**
 * Suffix query: find rows where Col_FuzzyKeyword ends with "hangzhou".
 */
private static void suffixQuery(SyncClient client) {
    SearchQuery searchQuery = new SearchQuery();
    SuffixQuery suffixQuery = new SuffixQuery(); // Set the query type to SuffixQuery.
    searchQuery.setGetTotalCount(true);
    suffixQuery.setFieldName("Col_FuzzyKeyword");
    suffixQuery.setSuffix("hangzhou");
    searchQuery.setQuery(suffixQuery);
    //searchQuery.setGetTotalCount(true); // Specify that the total number of matched rows is returned.

    SearchRequest searchRequest = new SearchRequest("<TABLE_NAME>", "<SEARCH_INDEX_NAME>", searchQuery);
    // You can configure the columnsToGet parameter to specify the columns to return or specify that all columns are returned. If you do not configure this parameter, only the primary key columns are returned.
    SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
    //columnsToGet.setReturnAll(true); // Specify that all columns are returned.
    columnsToGet.setColumns(Arrays.asList("Col_FuzzyKeyword")); // Specify the columns that you want to return.
    searchRequest.setColumnsToGet(columnsToGet);

    SearchResponse resp = client.search(searchRequest);
    //System.out.println("TotalCount: " + resp.getTotalCount()); // Specify that the total number of matched rows instead of the number of returned rows is displayed.
    System.out.println("Row: " + resp.getRows());
}

Billing rules

When you use a search index to query data, you are charged for the read throughput that is consumed. For more information, see Billable items of search indexes.

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.