通过SQL查询多元索引数组类型的数据_表格存储(Tablestore)-阿里云帮助中心

多元索引除了提供Long、Double、Boolean、Keyword、Text等基本类型外，还提供了数组类型。数组类型属于附加类型，可以附加在Long、Double、Boolean、Keyword、Text等基本类型之上。例如Long类型+数组后，即为数组长整型。本文介绍如何通过SQL查询功能查询数组类型的列数据。

说明

关于数组类型的更多信息，请参见数组类型。

注意事项

SQL查询目前不支持多元索引Geo-point类型。
要使用数组类型，在数据表中的列数据类型必须为字符串。创建多元索引时，该列的数据类型需要设置为数组元素的实际类型并且开启该列的数组属性。

数据类型映射

数据表中数据类型	多元索引中数据类型	SQL数据类型
字符串	数组元素的实际类型，同时开启该列的数组属性（即在控制台打开对应列的数组选项或使用SDK将对应列的IsArray设置为true）	VARCHAR（主键） MEDIUMTEXT（预定义列）

创建方法

如果要在SQL查询时使用数组类型，则必须创建多元索引映射关系。关于创建多元索引映射关系的具体操作，请参见创建多元索引的映射关系。

在CREATE TABLE语句中数组列需要正确设置数组列名和对应的SQL数据类型。在多元索引映射关系时，推荐定义数组列为MEDIUMTEXT类型。

创建包含数组类型列的多元索引映射关系，SQL示例如下：

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

通过SQL查询数据

通过SQL查询数组类型时需要使用ARRAY_EXTRACT(col_name)函数实现，其中col_name为数组列名。该函数支持与运算符组合使用作为查询条件，例如ARRAY_EXTRACT(col_long) = 1。

重要

通过SQL查询数组类型时，不能直接使用数组列与运算符组合使用。

假设要使用SQL查询col_long数组列的数据，数据表中该列为字符串类型，多元索引中该列类型为Long数组，查询条件为数组元素值等于1。SQL示例如下：

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

使用限制

ARRAY_EXTRACT(col_name)函数作为数组查询条件时只能在多元索引映射关系上使用，且只能设置一个数组列参数。该函数只能作为SELECT语句的WHERE子句，不能作为SELECT语句的列表达式，不能用于聚合函数计算，不能进行排序。
数组列可以作为SELECT语句的列名或者列表达式，但不能用于聚合函数计算，不能进行排序。
使用ARRAY_EXTRACT(col_name)函数与运算符组合使用作为查询条件时，不能进行数据类型转换后的计算，请确保数组列对应的数据类型正确。例如长整型数组列col_long_array支持使用array_extract(col_long_array) = 1进行计算，不能使用array_extract(col_long_array) = '1'进行计算。
如果数组元素为Text类型，则需要结合全文检索的text_match()或text_match_phrase()函数使用，例如text_match(array_extract(col_text), "xxx")。关于全文检索的更多信息，请参见全文检索。

使用示例

假设数据表名称为test_table，该表中有col_keyword_array（字符串类型）和col_long_array（字符串类型）两列。

如果要使用SQL语句查询数组类型的数据，则需要为数据表创建多元索引并创建多元索引的映射关系，然后使用SQL语句查询数组类型列的数据。具体步骤如下：

创建一个多元索引。具体操作，请参见使用控制台创建多元索引或使用SDK创建多元索引。
多元索引名称为test_table_index，该多元索引包括col_keyword_array（字符串数组类型）和col_long_array（长整型数组类型）两列。
使用控制台创建多元索引的配置如下图所示。
创建多元索引映射关系。更多信息，请参见创建多元索引的映射关系。
多元索引映射关系名称为test_table__test_table_index，该映射关系中col_keyword_array和col_long_array列对应的SQL数据类型均为MEDIUMTEXT。
SQL示例如下：
```
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`;
```
返回结果如下图所示。假设多元索引映射关系test_table__test_table_index有5条数据。
使用SELECT语句查询数据。
查询col_long_array数组列中有等于1的元素或col_keyword_array数组列中有以"d"开头的元素的数据。
```
SELECT * FROM test_table__test_table_index WHERE ARRAY_EXTRACT(col_long_array) = 1 OR ARRAY_EXTRACT(col_keyword_array) like 'd%';
```
返回结果如下图所示。