To perform full-text search, you can use the conditions of match query (TEXT_MATCH) or match phrase query (TEXT_MATCH_PHRASE) as the WHERE clause in the SELECT statement to query data that matches the specified character string in the table.

Prerequisites

A search index is created for the table whose data you want to query, and tokenization is performed for the column that you want to query. For more information, see Create search indexes.

Note For more information about tokenization, see Tokenization.

Match query

This query uses approximate matches to retrieve query results. Tablestore tokenizes the values in TEXT columns and the keywords that you use to perform match queries based on the analyzer that you specify. This way, Tablestore can perform match queries based on the tokens. We recommend that you use TEXT_MATCH_PHRASE to achieve high performance when you perform fuzzy query to query columns 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 column that you want to query. You can perform match queries on TEXT columns.
    text string Yes "tablestore is cool" The keyword that is used to match the column values when you perform a match query.

    If the column to query is a TEXT column, the keyword is tokenized into multiple tokens based on the analyzer that you specify when you create the search index. By default, single-word tokenization is performed if you do not specify the analyzer when you create the search index.

    For example, if you set the tokenization method to single-word tokenization and 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 match queries. 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 a column value. Default value: 1.

      If you set operator to OR, a row meets the query conditions only if the value of the fieldName column 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 column value 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 shows how to query the rows in which the value of the col1 column matches at least two tokens of the "tablestore is cool" string in the exampletable table: 

    SELECT * FROM exampletable WHERE TEXT_MATCH(col1, "tablestore is cool", "or", "2")
    The following sample code shows how to query the rows in which the value of the col1 column matches all tokens of the "tablestore is cool" string in the exampletable table: 
    SELECT * FROM exampletable WHERE TEXT_MATCH(col1, "tablestore is cool", "and")

Match phrase query

Match phrase query is similar to match query (TEXT_MATCH), but evaluates the position between multiple tokens. A row meets the query conditions only when the order and positions of the tokens in the row match the order and positions of the tokens that are contained in the tokenized keyword.

  • SQL expression
    TEXT_MATCH_PHRASE(fieldName, text)
  • Parameters
    Parameter Type Required Example Description
    fieldName string Yes col1 The name of the column that you want to query. You can perform match phrase queries on TEXT columns.
    text string Yes "tablestore is cool" The keyword that is used to match the column values when you perform a match phrase query.

    If the column to query is a TEXT column, the keyword is tokenized into multiple tokens based on the analyzer that you specify when you create the search index. By default, single-word tokenization is performed if you do not set the analyzer when you create the search index.

    For example, if you query the phrase "this is", "..., this is tablestore" and "this is a table" are returned. "this table is ..." and "is this a table" are 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.

  • Example
    The following sample code shows how to query the rows in which the value of the col1 column matches the "tablestore is cool" string in the exampletable table: 
    SELECT * FROM exampletable WHERE TEXT_MATCH_PHRASE("col1", "tablestore is cool")