You can use a match query or match phrase query condition as the WHERE clause in a SELECT statement and execute the SELECT statement to query data in a table that matches a specific string by using a search index that is created for the table. This way, you can perform full-text search.
Prerequisites
A search index is created for the table whose data you want to query, and tokenization is performed for the field that you want to query. For more information, see Create a search index.
For more information about tokenization, see Tokenization.
Match query
You can use match query to query data in a table based on approximate matches. Tablestore tokenizes the values in a TEXT field and the keyword you use to perform a match query based on the analyzer type that you specify. This way, Tablestore can perform a match query based on the tokens. We recommend that you use match phrase query to achieve high performance when you perform a fuzzy query to query fields for which fuzzy tokenization is performed.
SQL expression
TEXT_MATCH(fieldName, text, [options])Parameters
Parameter
Type
Required
Example
Description
fieldName
string
Yes
col1
The name of the field that you want to match. Match query applies to TEXT fields.
text
string
Yes
"tablestore is cool"
The keyword that is used to match the value of the field when you perform a match query.
If the field that you want to match is a TEXT field, the keyword is tokenized into multiple tokens based on the analyzer type that you specify when you create the search index. If you do not specify the analyzer type when you create the search index, single-word tokenization is performed.
For example, if the field that you want to match is a TEXT field, you set the analyzer type to single-word tokenization, and you use "this is" as a search keyword, you can obtain query results such as "..., this is tablestore", "is this tablestore", "tablestore is cool", "this", and "is".
options
string
No
"or", "2"
The options that you want to use to perform a match query. Valid values:
operator: the logical operator. Valid values: OR and AND. Default value: OR.
minimum_should_match: the minimum number of matched tokens that are contained in the value of the field. Default value: 1.
If you set operator to OR, a row meets the query conditions only if the value of the field specified by the fieldName parameter in the row contains at least the minimum number of matched tokens.
If you set operator to AND, a row meets the query conditions only if the value of the field specified by the fieldName parameter in the row contains all tokens.
Return value
The return value indicates whether the row meets the query conditions. The return value is of the Boolean type. If the return value is true, the row meets the query conditions. If the return value is false, the row does not meet the query conditions.
Examples
The following sample code provides an example on how to use match query to query data in the exampletable table. In this example, the rows in which the value of the col1 column matches at least two tokens of the "tablestore is cool" string are queried.
SELECT * FROM exampletable WHERE TEXT_MATCH(col1, "tablestore is cool", "or", "2")The following sample code provides an example on how to use match query to query data in the exampletable table. In this example, the rows in which the value of the col1 column matches all tokens of the "tablestore is cool" string are queried.
SELECT * FROM exampletable WHERE TEXT_MATCH(col1, "tablestore is cool", "and")
Match phrase query
A match phrase query is similar to a match query, except that a match phrase query evaluates the positions of tokens. A row meets the query conditions only if the order and positions of the tokens in the row match the order and positions of the tokens that are contained in the keyword.
SQL expression
TEXT_MATCH_PHRASE(fieldName, text)Parameters
Parameter
Type
Required
Example
Description
fieldName
string
Yes
col1
The name of the field that you want to match. Match phrase query applies to TEXT fields.
text
string
Yes
"tablestore is cool"
The keyword that is used to match the value of the field when you perform a match phrase query.
If the field that you want to match is a TEXT field, the keyword is tokenized into multiple tokens based on the analyzer type that you specify when you create the search index. If you do not specify the analyzer type when you create the search index, single-word tokenization is performed. For more information, see Tokenization.
For example, if you perform a match phrase query by using the phrase "this is", "..., this is tablestore" and "this is a table" are returned. "this table is ..." or "is this a table" is not returned.
Return value
The return value indicates whether the row meets the query conditions. The return value is of the Boolean type. If the return value is true, the row meets the query conditions. If the return value is false, the row does not meet the query conditions.
Examples
The following sample code provides an example on how to use match phrase query to query data in the exampletable table. In this example, the rows in which the value of the col1 column matches the "tablestore is cool" string are queried.
SELECT * FROM exampletable WHERE TEXT_MATCH_PHRASE(col1, "tablestore is cool")
References
You can also use a search index to perform full-text search. For more information, see Tokenization, Match query, and Match phrase query.