Tablestore:ARRAY supported in search indexes

Usage notes

• You cannot query data of GEOPOINT fields in search indexes by executing SQL statements.

• If you want to query the data of an ARRAY field, the data of the ARRAY field in the relevant table must be of the STRING type. When you create a search index for a field, you must set the data type of the index field to the actual data type of the array element and enable the array attribute for the field.

Data type mappings

Create a mapping table for a search index

If you want to query the data of an ARRAY field by executing SQL statements, you must create a mapping table for the search index. For more information about how to create a mapping table for a search index, see Create mapping tables for search indexes.

In the CREATE TABLE statement, you must specify a valid name and the mapped data type in SQL for the ARRAY field. We recommend that you specify MEDIUMTEXT as the data type of the ARRAY field in SQL when you create a mapping table for the search index.

The following sample SQL statement provides an example on how to create a mapping table for a search index that contains ARRAY fields:

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"}';

Query data by executing SQL statements

To query the data of an ARRAY field, you must use the ARRAY_EXTRACT(col_name) function. col_name in this function indicates the name of an ARRAY field. You can use this function together with operators to specify query conditions. For example, you can specify ARRAY_EXTRACT(col_long) = 1 as the query condition.

If you want to query the data of the ARRAY field col_long, the data of col_long in the table is of the STRING type, the data of col_long in the search index is of the LONG type, and the query condition is that the array element contains the value of 1, you can execute the following sample SQL statement:

SELECT * FROM table_name WHERE ARRAY_EXTRACT(col_long) = 1; 

Limits

• When you use the ARRAY_EXTRACT(col_name) function to specify a query condition to query data of AARAY fields, you can use this function only in the mapping table of the search index and specify only one ARRAY field. This function can be used only 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.

• An ARRAY field can be used as the field name 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 operators to specify query conditions, you cannot perform calculations after the data type is converted. Make sure that the data type of the ARRAY field is valid. For example, you can calculate the values of array elements in col_long_array that is an array of the LONG INTEGER type by using the array_extract(col_long_array) = 1 query condition. You cannot calculate such values by using the array_extract(col_long_array) = '1' query condition.

• If the array elements are of the TEXT type, you need to use the function together with the text_match() or text_match_phrase() function that is used for full-text search. Example: text_match(array_extract(col_text), "xxx"). For more information about full-text search, see Full-text search.

Example

The following example shows how to query data of the test_table table that contains two ARRAY fields: col_keyword_array and col_long_array. The data of the two fields are of the STRING type.

If you want to execute SQL statements to query data of the ARRAY fields in the test_table table, you must create a search index for the table and create a mapping table for the search index. To do so, perform the following steps:

1. Create a search index. For more information, see Use the Tablestore console or Use search indexes by using Tablestore SDKs.

   The search index is named test_table_index. The search index contains two fields: col_keyword_array and col_long_array. The data of col_keyword_array is of the STRING type and the data of col_long_array is of the LONG INTEGER type.

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

   

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

   The mapping table of the search index is named test_table__test_table_index. The SQL data types of the col_keyword_array and col_long_array fields in the mapping table are both MEDIUMTEXT.

   Sample SQL statement:

   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.

   Query the array elements whose values contain 1 in col_long_array or the array elements whose values start with d in col_keyword_array.

   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.