The core features of search indexes include queries based on primary key columns and non-primary key columns, Boolean query, Geo query, full-text search, fuzzy query, prefix query, exists query, nested query, collapse (distinct), sorting, match all query, and aggregation. You can use these features based on your business requirements. Some features of search indexes have equivalent SQL statements.
Core features
Search indexes support multi-dimensional data queries and common data statistical analysis. The following table describes the core features of search indexes.
Feature | Description | References |
Queries based on primary key columns and non-primary key columns | Queries that are based on only primary key columns or prefixes of primary key columns cannot meet requirements in some scenarios. If you want to query data based on a non-primary key column, you can include the column in a search index and perform queries based on the non-primary key column. | |
Boolean query | Boolean queries are suitable for order scenarios. In order scenarios, a table may contain dozens of fields. It may be difficult to determine how to combine the fields required for queries when you create a table. Even if you can determine how to combine the required fields, hundreds of combinations may be available. If you use a relational database service, you may have to create hundreds of indexes. In addition, if no index is created for a specific combination in advance, you cannot query the required data. You can use Tablestore to create a search index that includes all the fields that may be required and freely combine these fields in queries. Search indexes also support logical operators such as AND, OR, and NOT. | |
Geo query | As mobile devices gain popularity, geographical location data becomes increasingly important. This data is used in most apps such as social media apps, food delivery apps, sports apps, and Internet of Vehicles (IoV) apps. These apps must support query features for the geographical location data that these apps provide. Search indexes support queries based on the following geographical location data:
Tablestore allows you to use these features to query geographical location data without the need to use other database services or search engines. | |
Full-text search | Search indexes can tokenize data to support full-text search. However, unlike search engines, Tablestore returns only BM25-based results. Tablestore does not return custom relevant results in response to a query. If you need to query relevant results, we recommend that you use search engines. Search indexes support the following tokenization methods: single-word tokenization, delimiter tokenization, minimum semantic unit tokenization, maximum semantic unit tokenization, and fuzzy tokenization. You can select tokenization methods based on your requirements. | |
Fuzzy query | Search indexes support 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 ( | |
Prefix query | Search indexes support prefix queries. For example, in a query based on prefix | |
Exists query | An exists query is also called a NULL query or a NULL-value query. This type of query is used in sparse data to determine whether a column of a row exists. | |
Nested query | In addition to a flat structure, online data such as tagged pictures has complex and 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 weight score. The score is evaluated based on the size and position of an element in a picture. Therefore, each picture has multiple tags. Each tag has a name and a weight score. You can use nested queries to query data based on the data tags. Image tags are stored in the JSON format. Example:
Nested queries can be used to query data with multiple layers of logical relationships and greatly facilitate the modeling of complex data. | |
Collapse (distinct) | Search indexes support the collapse (distinct) feature. This feature allows you to specify the highest frequency of occurrence of an attribute value to achieve high cardinality. For example, in e-commerce scenarios, if you search for | |
Sorting | Tablestore allows you to sort data in alphabetical order based on the primary key. If you want to sort data based on other fields, you can use the sorting feature of search indexes. Search indexes support sorting in ascending order or descending order based on one or more conditions. The sorting is globally performed on all data in a search index. By default, the returned results of a search index are sorted based on the primary key in the data table. | |
Match all query | You can use search indexes to return the total number of rows that match the query conditions. This feature applies to data verification and data-driven operations.
| |
Aggregation | Search indexes allow you to perform aggregation operations to obtain the maximum value, minimum value, average value, sum, count and distinct count of rows, percentiles, and histogram statistics. You can also use search indexes to group results. This allows you to perform lightweight statistical analysis. |
Mappings between SQL statements and the features of search indexes
Some features of search indexes have equivalent SQL statements. The following table describes the mappings between SQL statements and the features of search indexes.
SQL | Search index feature | References |
Show | API operation: DescribeSearchIndex | |
Select | Parameter: ColumnsToGet | |
From | Parameter: index name Important Single-column indexes are supported. Multi-column indexes are not supported. | |
Where | Query: a variety of query methods such as term query | |
Order by | Parameter: sort | |
Limit | Parameter: limit | |
Delete | API operation: DeleteRow after a query |
|
Like | Query: WildcardQuery | |
And | Parameter: operator = and | |
Or | Parameter: operator = or | |
Not | Query: BoolQuery(mustNotQueries) | |
Between | Query: RangeQuery | |
Null | Query: ExistsQuery | |
In | Query: TermsQuery | |
Min | Aggregation: min | |
Max | Aggregation: max | |
Avg | Aggregation: avg | |
Count | Aggregation: count | |
Count(distinct) | Aggregation: distinctCount | |
Sum | Aggregation: sum | |
Group By | GroupBy |