This topic describes the core features of search index.

Core features

Query based on non-primary key columns

Originally, Table Store only supports queries based on complete primary key columns or their prefixes. Queries based on non-primary key columns were not available in some scenarios. Search index enables Table Store to support queries based on non-primary key columns. You only need to create a search index for the column to be queried.

Bool query

Bool query is applicable to order scenarios. In order scenarios, a table may contain dozens of fields. You cannot determine how to combine fields required for queries when you create a table. Even if the combination of required fields is specified, hundreds of combinations may be available. If you use a relational database, you need to create hundreds of indexes. In addition, if a certain combination is not created in advance, you cannot query the corresponding data.

However, you can use Table Store to create a search index that includes the required field names, which can be combined in a query as needed. Search index also supports multiple logical operators, such as AND, OR, and NOT.

Query by geographical location

With the popularization of mobile devices, geographical location data is becoming increasingly important. The data is used in most apps, such as WeChat Moments, Sina Weibo, food delivery apps, sports apps, and Internet of Vehicles (IoV) apps. These apps provide geographical location data. Therefore, they must support query features.

Search index supports queries based on the following geographical location data:
  • Near: queries points within a specified radius based on a central point.
  • Within: queries points within a specified rectangular or polygonal area.

Based on these query features, you can use Table Store to easily query geographical location data without resorting to other databases or search engines.

Full-text search

Search index can tokenize data to perform full-text search. However, unlike search engines, Table Store cannot return relevant results in response to a query. Therefore, if you need relevant results, we recommend that you use search engines.

Five tokenization types are available, including single-word tokenization, delimiter tokenization, minimum semantic unit-based tokenization, maximum semantic unit-based tokenization, and fuzzy tokenization. For more information, see Tokenization.

Fuzzy query

Search index supports queries based on wildcards. This feature is similar to the LIKE operator in relational databases. You can specify characters and wildcards such as question marks (?) or asterisks (*) to query data in the way similar to the LIKE operator.

Prefix query

Search index supports the prefix query feature. This feature is applicable to any natural language. For example, in the query based on the prefix "apple", the system may return words such as "apple6s" and "applexr".

Nested query

In addition to a flat structure, online data such as labeled pictures have some complex multilayered structures. For example, a database stores a large number of pictures, and each picture has multiple elements, such as houses, cars, and people. Each element in a picture has a unique score. The score is evaluated based on the size and position of the element in a picture. Therefore, each picture has multiple labels. Each label has a name and a weighted score. You can use nested queries based on the conditions or field names of the labels.

The following example shows the JSON data format in a query:
{
 "tags": [
   {
      "name": "car",
      "score": 0.78
   },
   {
      "name": "tree",
      "score": 0.24
   }
 ]
}

You can use the nested query effectively to store and query data of multilayered logical relationships. This query facilitates the modeling of complex data.

Deduplication

Search index supports deduplication for query results. Deduplication allows you to specify the highest frequency of occurrence of an attribute value to achieve high cardinality. For example, when you search for a laptop on an e-commerce platform, the first page may display products of a certain brand. This is not a user-friendly result. However, the deduplication feature of Table Store can resolve this issue.

Sorting

A table sorts data based on the alphabetical order of primary key columns. To sort data by other fields, you need to use the sorting feature of search index. Table Store supports multiple types of sorting, such as ascending sorting, descending sorting, single-field sorting, and multi-field sorting. By default, Table Store returns results based on the order of primary key columns. You can use this method to sort global data.

Total number of rows

You can specify the number of rows that the system returns for the current request when you use search index for a query. If you do not specify any query condition for search index, the system returns the total number of rows where you have created indexes. When you stop writing new data to a table and create indexes on all attributes, the system returns the total number of rows in the table. This feature applies to data verification and data management.

SQL

Table Store does not support SQL statements and operators. However, most of these SQL features can match similar features of search index, as shown in the following table.
SQL Search index Supported
SHOW API operation: DescribeSearchIndex Yes
SELECT Parameter: ColumnsToGet Yes
FROM Parameter: index name Supported for single indexes and not supported for multiple indexes
WHERE Query: a variety of queries such as TermQuery Yes
ORDER BY Parameter: sort Yes
LIMIT Parameter: limit Yes
DELETE API operation: query followed by DeleteRow Yes
LIKE Query: wildcard query Yes
AND Parameter: operator = and Yes
OR Parameter: operator = or Yes
NOT Query: bool query Yes
BETWEEN Query: range query Yes
NULL ExistQuery Yes