Tablestore provides the following core features: query based on random primary key columns and non-primary key columns, Boolean query, geo query, full-text search, fuzzy query, prefix query, nested query, collapse (distinct), sorting, match all query, and aggregation. You can use the features based on your business requirements. Some features of search indexes are equivalent to specific 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. | Documents of query methods provided by search indexes, such as Term query |
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. Geographical location data is used in various apps such as social media apps, food delivery apps, sports apps, and Internet of Vehicles (IoV) apps. These apps require query features to provide for geographical location data. 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. You can use search indexes to sort query results based on only the BM25-based keyword relevance score. If you want to sort query results based on other relevance scores, we recommend that you use search systems. 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. | |
KNN vector query | Search indexes provide the KNN vector query feature. You can find the most similar data items in large-scale datasets by using vectors to perform approximate nearest neighbor searches. This feature is suitable for scenarios such as recommendation systems, image and video retrieval, and natural language processing (NLP). | |
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 the 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, data of online applications, such as tagged images, has complex and multilayered structures. For example, a database stores a large number of images, and each image 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 an image. Therefore, each image has multiple tags. Each tag has a name and a weight score. You can use nested queries to query data based on the tag conditions. Image tags are stored in the JSON format. Example:
Nested queries can be used to query data that has multiple layers of logical relationships. This greatly facilitates the modeling of complex data. | |
Collapse (distinct) | Search indexes support the collapse (distinct) feature. You can use the collapse (distinct) feature to collapse the result set based on a specific column. This way, data of the specified type appears only once in the query results to ensure the diversity of the result types. For example, in e-commerce scenarios, if you search for | |
Sorting | Tablestore provides the sorting feature based on the primary key of data. 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 | Documents of query methods provided by search indexes, such as Term query |
From | Parameter: index name Important Single-column indexes are supported. Multi-column indexes are not supported. | Documents of query methods provided by search indexes, such as Term query |
Where | Query: a variety of query methods such as term query | Documents of query methods provided by search indexes, 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 |