Search data by a specified suffix - Tablestore - Alibaba Cloud Documentation Center

How it works

To run a SuffixQuery, specify the suffix value to match.

Only the FuzzyKeyword data type supports suffix queries. FuzzyKeyword is optimized for fuzzy search operations such as SuffixQuery, PrefixQuery, and wildcard query, and delivers stable performance regardless of data volume.

Note

Fields of the FuzzyKeyword type do not support sorting or aggregation. If you need to sort or perform aggregation on a FuzzyKeyword field, you can create a virtual column of the Keyword type for this purpose.
To simulate a suffix query on a Keyword field, reverse the string when writing data and then use a prefix query (PrefixQuery) to search for the data.

API

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

Parameters

Search request

request is a SearchRequest object that contains the following parameters.

Name	Type	Description
tableName (required)	String	The name of the data table.
indexName (required)	String	The name of the search index.
searchQuery (required)	SearchQuery	The query condition and general query settings.
columnsToGet (optional)	SearchRequest.ColumnsToGet	The columns to return. If this parameter is not configured, only primary key columns are returned.
timeoutInMillisecond (optional)	int	The request-level query timeout in milliseconds. The default value is `-1`, which does not configure a separate query timeout.
routingValues (optional)	`List<PrimaryKey>`	The primary key values that correspond to custom routing fields. Leave this parameter unset if custom routing is not configured.

Query settings

request.searchQuery is a SearchQuery object that contains the following parameters.

Name	Type	Description
query (required)	Query	The query condition. Set this parameter to a `SuffixQuery` object for a suffix query.
offset (optional)	Integer	The position from which to start the current query.
limit (optional)	Integer	The maximum number of rows to return. Set this parameter to `0` to return no rows.
collapse (optional)	Collapse	The collapse configuration used to deduplicate results by a specified field. For more information, see Collapse (distinct).
sort (optional)	Sort	The sort order of the results. `FuzzyKeyword` fields cannot be used as sort fields. For more information, see Sorting and pagination.
trackTotalCount (optional)	int	The maximum number of matching rows to count. The default value is `TRACK_TOTAL_COUNT_DISABLED`, which disables counting. Set this parameter to `TRACK_TOTAL_COUNT` to count all matching rows. A smaller value provides better query performance.
filter (optional)	SearchFilter	A post-query filter applied to the results of `query`.
aggregationList (optional)	`List<Aggregation>`	The aggregation configurations. `FuzzyKeyword` fields cannot be used as aggregation fields. For more information, see Aggregation.
groupByList (optional)	`List<GroupBy>`	The grouping configurations. `FuzzyKeyword` fields cannot be used as grouping fields. For more information, see Aggregation.
token (optional)	byte[]	The pagination token. Set the `nextToken` from the previous response as this parameter to read the next page. When `token` is set, the SDK clears `sort` because the token already contains the sort condition.

Query condition

request.searchQuery.query is a SuffixQuery object that contains the following parameters.

Name	Type	Description
fieldName (required)	String	The name of the `FuzzyKeyword` field to query.
suffix (required)	String	The query string. The complete field value must end with this string. Matching is case-sensitive.
weight (optional)	float	The relevance weight of the query condition. The value must be a positive floating-point number. A larger value gives the condition more influence on the BM25 relevance score. This parameter does not change which rows match or how many rows are returned. It affects result order only when `ScoreSort` is used. The default value is `1.0`.

Returned columns

request.columnsToGet is a SearchRequest.ColumnsToGet object that contains the following parameters.

Name	Type	Description
columns (optional)	`List<String>`	The attribute columns to return. Configure this parameter only if both `returnAll` and `returnAllFromIndex` are `false`. If this parameter is not configured, only primary key columns are returned.
returnAll (optional)	boolean	Specifies whether to return all attribute columns from the data table. The default value is `false`.
returnAllFromIndex (optional)	boolean	Specifies whether to return all indexed attribute columns. The default value is `false`. Do not set both `returnAll` and `returnAllFromIndex` to `true`.

Usage

Perform a suffix query by using the Tablestore console or an SDK. Before you begin, complete the following prerequisites:

You have created a RAM user and granted it permissions to access Tablestore. For more information, see Use the AccessKey pair of a RAM user to access Tablestore.
You have created a data table. For more information, see Operations on data tables.
You have created a search index for the data table. For more information, see Create a search index.

Use the Tablestore console

Go to the Index Management tab.
1. Log on to the Table Store console.
2. In the top navigation bar, select a resource group and a region.
3. On the Overview page, click the instance name or click Instance Management in the Actions column.
4. On the Instance Details tab, in the Data Table List tab, click the data table name or click Index Management in the Actions column.
On the Indexes tab, find the target search index and click Manage Data in the Actions column.
1. By default, all columns are returned. To return specific columns, turn off Retrieve All Columns and enter the column names, separated by commas.
  Note
  By default, Table Store returns the primary key columns of the data table.
2. Select a logical operator: And, Or, or Not.
  If you select And, the query returns data that meets all specified conditions. If you select Or, the query returns data that meets at least one of the specified conditions. If you select Not, the query returns data that does not meet the specified conditions.
3. Select an index field of the FuzzyKeyword type and click Add.
4. Set the query type for the index field to Suffix query (SuffixQuery) and enter the suffix value.
5. By default, sorting is disabled. To sort the results by a specific field, turn on Enable Sorting, add the sort field, and configure the sort order.
6. By default, aggregation is disabled. To perform statistical aggregation on a specific field, turn on Enable Aggregation, add the field for aggregation, and configure the aggregation settings.
Click OK.
The query results are displayed on the Index Management tab.

Use an SDK

The following example uses the Tablestore SDK for Java.

Important

The suffix query feature is supported in Tablestore SDK for Java 5.17.0 and later.
Before you perform a suffix query using the SDK, you must initialize a client. For more information, see Initialize a Tablestore client.

The following example queries FuzzyKeyword values in the phone field that end with 1234. The query returns up to 10 rows and the total number of matches.

String tableName = "example_table";
String indexName = "example_index";

SuffixQuery suffixQuery = new SuffixQuery();
suffixQuery.setFieldName("phone");
suffixQuery.setSuffix("1234");

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(suffixQuery);
searchQuery.setLimit(10);
searchQuery.setTrackTotalCount(SearchQuery.TRACK_TOTAL_COUNT);

SearchRequest request = new SearchRequest(tableName, indexName, searchQuery);
SearchRequest.ColumnsToGet columnsToGet = new SearchRequest.ColumnsToGet();
columnsToGet.setReturnAll(true);
request.setColumnsToGet(columnsToGet);

SearchResponse response = client.search(request);
System.out.println(response.getTotalCount());
System.out.println(response.getRows());

Billing

Querying data by using a Search Index consumes read throughput. For more information, see Search Index metering and billing.

Tablestore:Suffix query