Wildcard query - Tablestore SDK for Java - Tablestore - Alibaba Cloud Documentation Center

Prerequisites

Install the Tablestore SDK for Java and initialize a client.

Feature description

A wildcard query matches an indexed field against a pattern that contains wildcard characters. Its matching semantics are similar to SQL LIKE, but the pattern uses * and ? as wildcard characters. For a Keyword or FuzzyKeyword field, the pattern is matched against the complete field value. For a Text field, the pattern is matched against each token generated from the field value, and the pattern itself is not tokenized. For more information about the supported field types, see String types. Matching is case-sensitive.

A pattern can start with a wildcard character and supports the following wildcard characters:

* matches zero or more characters.
? matches any single character.

For example, table*e matches tablestore. The pattern hang*u matches hangu and hangzhou. The pattern hang?u matches hangxu but not hangu.

To match values that contain a specified string, such as with a *word* pattern (equivalent to SQL WHERE field_a LIKE '%word%'), use a token-based wildcard query. With this approach, query performance does not degrade as the data volume grows.

Note

To exclude data that matches a pattern, add the WildcardQuery object to BoolQuery.mustNotQueries. This configuration is equivalent to the SQL NOT LIKE operator. For information about configuring BoolQuery, see Boolean query.

Set the query type to WildcardQuery when you call search. Use SearchQuery to configure the result limit, total count tracking, and other general query settings.

SearchResponse search(SearchRequest request)

The following example queries Keyword values in the product_name field that match the table*e pattern. The query returns up to 10 rows and the total number of matches.

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

WildcardQuery wildcardQuery = new WildcardQuery();
wildcardQuery.setFieldName("product_name");
wildcardQuery.setValue("table*e");

SearchQuery searchQuery = new SearchQuery();
searchQuery.setQuery(wildcardQuery);
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());

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 `WildcardQuery` object for a wildcard query.
offset (optional)	Integer	The starting position of the query.
limit (optional)	Integer	The maximum number of rows to return. Set this parameter to `0` to return no rows.
highlight (optional)	Highlight	The summary and highlighting settings for `Text` fields. For configuration details, see Summary and highlighting.
collapse (optional)	Collapse	The field collapse settings, which deduplicate results by a specified field. For configuration details, see Collapse query results.
sort (optional)	Sort	The result sort order. For configuration details, see Sort and paginate results.
trackTotalCount (optional)	int	The expected 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 improves query performance.
filter (optional)	SearchFilter	A filter that is applied to the results of `query`.
aggregationList (optional)	`List<Aggregation>`	The aggregation settings. For configuration details, see Aggregation.
groupByList (optional)	`List<GroupBy>`	The grouping settings. For configuration details, see Aggregation.
token (optional)	byte[]	The pagination token. Set this parameter to the `nextToken` value from the previous response to continue reading rows. When you set `token`, the SDK clears `sort` because the token already contains the sort conditions.

Query condition

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

Name	Type	Description
fieldName (required)	String	The name of the `Keyword`, `Text`, or `FuzzyKeyword` field to query.
value (required)	String	A query pattern that contains wildcard characters. The maximum length is 32 characters, and matching is case-sensitive. For a `Keyword` or `FuzzyKeyword` field, the pattern is matched against the complete field value. For a `Text` field, the pattern is matched against each token, and the pattern itself is not tokenized.
weight (optional)	float	The relevance weight of the query condition. The value must be a positive floating-point number. A larger value gives the query condition a greater contribution to the BM25 relevance score. This parameter does not affect matching or the number of rows returned. It affects result order only when `ScoreSort` is used to sort by relevance score. Default value: `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. Set this parameter only if `returnAll` and `returnAllFromIndex` are both `false`. If you omit this parameter, 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`.

Return values

search returns a SearchResponse object. The following table describes the core fields.

Name	Type	Description
totalCount	long	The number of matching rows. Call `getTotalCount()` to obtain the value. The returned value depends on the `trackTotalCount` setting.
rows	`List<Row>`	The rows returned by this query. Call `getRows()` to obtain the value. The number of rows does not exceed `limit`.
searchHits	`List<SearchHit>`	The query hits. Call `getSearchHits()` to obtain the value. If `highlight` is configured, this field contains the row data and summary and highlighting results.
nextToken	byte[]	The next-page token. Call `getNextToken()` to obtain the value. If the value is not `null`, set it as `token` in the next request to continue reading rows.
isAllSuccess	boolean	Indicates whether all index partitions were queried successfully. Call `isAllSuccess()` to obtain the value. If the value is `false`, the response contains partial results and `totalCount` may be less than the actual number of matching rows.