Syntax and examples for querying data by using the search_query keyword-Lindorm(Lindorm)-阿里云帮助中心

SELECT statements support the search_query keyword for advanced queries and data retrieval. The syntax is similar to the query syntax of the Lucene full-text search engine library. This topic provides examples of how to query data using the search_query keyword.

Prerequisites

The search index service is enabled for the Lindorm instance. For more information, see Enable the search index service.
You are connected to the LindormTable engine using Lindorm-cli. For more information, see Connect to and use LindormTable using Lindorm-cli.
The minor version of the LindormTable engine is 2.2.17 or later. For information about how to perform a minor version update, see Minor version update.
You have prepared test data and created a search index. For more information, see Manage search indexes.

Syntax

SELECT selectors
  FROM table
  WHERE (search_query = 'search_expression')
  [ LIMIT n ];

Examples

OR logical query

SELECT * FROM search_table WHERE search_query = 'name:Ms. Chen age:[40 TO 60]';

This statement is equivalent to the following statement.

SELECT * FROM search_table WHERE name='Ms. Chen' OR (age >= 40 AND age <= 60);

The following result is returned:

+---------+----------+-----+--------+-----------------------------+------------------+----------+
| user_id |  name    | age | gender |           address           |      email       |   city   |
+---------+----------+-----+--------+-----------------------------+------------------+----------+
| 28      | Ms. Chen | 36  | F      | Nanshan District, Shenzhen  | a***@example.net | Shenzhen |
+---------+----------+-----+--------+-----------------------------+------------------+----------+

Logical AND query

SELECT * FROM search_table WHERE search_query = '+name:Mr. Zhang +age:[10 TO 40]';

This statement is equivalent to the following.

SELECT * FROM search_table WHERE name='Mr. Zhang' AND (age >= 10 AND age <= 40);

The following is the result:

+---------+-----------+-----+--------+----------------------------+------------------+---------+
| user_id |   name    | age | gender |          address           |      email       |  city   |
+---------+-----------+-----+--------+----------------------------+------------------+---------+
| 1       | Mr. Zhang | 18  | M      | Chaoyang District, Beijing | a***@example.net | Beijing |
+---------+-----------+-----+--------+----------------------------+------------------+---------+

Aggregation

SELECT count(*) FROM search_table WHERE search_query = 'age:[10 TO 40]';

The following result is returned:

+----------+
| COUNT(*) |
+----------+
| 4        |
+----------+

Tokenized query

SELECT * FROM search_table WHERE search_query='address:Beijing';

The following result is returned:

+---------+-----------+-----+--------+----------------------------+------------------+---------+
| user_id |   name    | age | gender |          address           |      email       |  city   |
+---------+-----------+-----+--------+----------------------------+------------------+---------+
| 1       | Mr. Zhang | 18  | M      | Chaoyang District, Beijing | a***@example.net | Beijing |
+---------+-----------+-----+--------+----------------------------+------------------+---------+

Sort query results. To sort the results by the specified age field, execute the following statement.

SELECT * FROM search_table WHERE search_query='{"q":"address:Hangzhou", "sort":"age asc"}';

Note

In the preceding statement, q and sort are system keywords. Do not modify them.

The following result is returned:

+---------+----------+-----+--------+------------------------------+------------------+----------+
| user_id |   name   | age | gender |           address            |      email       |   city   |
+---------+----------+-----+--------+------------------------------+------------------+----------+
| 20      | Mr. Wang | 28  | M      | Binjiang District, Hangzhou  | a***@example.net | Hangzhou |
| 6       | Mr. Li   | 32  | M      | Yuhang District, Hangzhou    | a***@example.net | Hangzhou |
+---------+----------+-----+--------+------------------------------+------------------+----------+

Elasticsearch-compatible syntax

SELECT * FROM search_table WHERE search_query='{"query": {
    "bool": {
      "must": {
        "match": {
          "address": "Beijing"
        }
      }
    }
  }
}';

This statement is equivalent to the following statement.

SELECT * FROM search_table WHERE MATCH (address) AGAINST ('Beijing');

The following result is returned:

+---------+-----------+-----+--------+----------------------------+------------------+---------+
| user_id |   name    | age | gender |          address           |      email       |  city   |
+---------+-----------+-----+--------+----------------------------+------------------+---------+
| 1       | Mr. Zhang | 18  | M      | Chaoyang District, Beijing | a***@example.net | Beijing |
+---------+-----------+-----+--------+----------------------------+------------------+---------+