Tablestore:Array type in search indexes

Usage notes

• You cannot execute SQL statements to query fields of the Geo-point type in a search index.

• You can specify Array as the type of a field in a search index only if the column to which the field is mapped in the data table is of the String type. When you create the search index, you must set the type of the field to the actual type of elements that you want to use an array to store and enable the Array attribute for the field.

Data type mappings

Prerequisites

A data table is created and data is written to the data table. For more information, see Operations on tables and Write data.

Procedure

If you want to query fields of the Array type by executing SQL statements, you must create a search index for a data table, create a mapping table for the search index, and then execute SQL statements to query fields of the Array type in the search index.

CREATE TABLE `test_table__test_table_index`(
    `col_keyword_array` MEDIUMTEXT,   
    `col_long_array` MEDIUMTEXT
) 
ENGINE='searchindex'
ENGINE_ATTRIBUTE='{"index_name":"test_table_index", "table_name":"test_table"}';

SELECT * FROM `test_table__test_table_index` WHERE ARRAY_EXTRACT(col_long_array) = 1; 

Limits

• You can use the ARRAY_EXTRACT(col_name) function as a condition to query a field of the Array type only when you execute SQL statements by using a mapping table that is created for the search index. You can specify only one field of the Array type in the function. The function can be used as a WHERE clause of a SELECT statement but cannot be used as a column expression of the SELECT statement, as an aggregate function, or for sorting.

• A field of the Array type can be used as the field or column expression in a SELECT statement but cannot be used as an aggregate function or for sorting.

• If you use the ARRAY_EXTRACT(col_name) function together with an operator as a query condition, you cannot perform calculation after the type of elements in the field of the Array type is converted. Make sure that the type of elements in the field of the Array type is valid. For example, the col_long_array field is of the Array type and stores elements of the Long type. You can use array_extract(col_long_array) = 1 as a query condition to perform calculation. However, you cannot use array_extract(col_long_array) = '1' as a query condition to perform calculation.

• If the type of elements in the field of the Array type is Text, you must use the ARRAY_EXTRACT(col_name) function together with the text_match() or text_match_phrase() function provided by full-text search. Example: text_match(array_extract(col_text), "xxx"). For information about full-text search, see Full-text search.

Examples

In this example, a data table named test_table is used. The data table contains the col_keyword_array column of the String type and the col_long_array column of the String type.

1. Create a search index. For more information, see Use the Tablestore console and Use Tablestore SDKs.

   In this example, a search index named test_table_index is created. The search index contains two fields of the Array type: the col_keyword_array field that stores elements of the Keyword type and the col_long_array field that stores elements of the Long type.

   The following figure shows how to create a search index in the Tablestore console.

   

2. Create a mapping table for the search index. For more information, see Create mapping tables for search indexes.

   In this example, a mapping table named test_table__test_table_index is created for the search index. In the mapping table, the type of the col_keyword_array and col_long_array fields is MEDIUMTEXT.

   You can execute the following sample SQL statement to create the mapping table:

   CREATE TABLE `test_table__test_table_index`(
       `col_keyword_array` MEDIUMTEXT,   
       `col_long_array` MEDIUMTEXT
   ) 
   ENGINE='searchindex'
   ENGINE_ATTRIBUTE='{"index_name":"test_table_index", "table_name":"test_table"}';

   After you create a mapping table for the search index, execute the following statement to query data from the mapping table:

   SELECT * FROM `test_table__test_table_index`;

   The following figure shows the query result. In this example, five data records are contained in the test_table__test_table_index mapping table.

   

3. Execute the SELECT statement to query data.

   In this example, the SELECT statement is executed to query the rows in which the col_long_array field of the Array type contains an element whose value is 1 or the rows in which the col_keyword_array field of the Array type contains an element whose value starts with "d".

   SELECT * FROM test_table__test_table_index WHERE ARRAY_EXTRACT(col_long_array) = 1 OR ARRAY_EXTRACT(col_keyword_array) like 'd%';

   The following figure shows the query result.