通過SQL查詢多元索引數群組類型的資料 - Tablestore

多元索引除了提供Long、Double、Boolean、Keyword、Text等基本類型外，還提供了數群組類型。數群組類型屬於附加類型，可以附加在Long、Double、Boolean、Keyword、Text等基本類型之上。使用ARRAY_EXTRACT條件作為SELECT語句中的WHERE子句，您可以使用SELECT語句通過多元索引查詢數群組類型列的資料。本文介紹如何通過SQL查詢功能查詢數群組類型列的資料。

說明

關於數群組類型的更多資訊，請參見數群組類型。

注意事項

SQL查詢目前不支援多元索引Geo-point類型列的查詢。
要使用數群組類型，在資料表中的列資料類型必須為字串。建立多元索引時，該列的資料類型需要設定為數組元素的實際類型並且開啟該列的數組屬性。

資料類型映射

資料表中資料類型	多元索引中資料類型	SQL資料類型
字串	數組元素的實際類型，同時開啟該列的數組屬性（即在控制台開啟對應列的數組選項開關或使用SDK將對應列的IsArray設定為true）	VARCHAR（主鍵） MEDIUMTEXT（預定義列）

前提條件

已建立資料表並寫入資料。具體操作，請參見資料表操作和寫入資料。

使用流程

如果要使用SQL語句查詢數群組類型的資料，則需要為資料表建立多元索引並建立多元索引的映射關係，然後使用SQL語句查詢數群組類型列的資料。

步驟一：建立多元索引

使用數群組類型時，您需要在建立多元索引時為所需列開啟數組選項。具體操作，請參見建立多元索引。

步驟二：建立多元索引的映射關係

如果要在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_array) = 1。

重要

通過SQL查詢數群組類型時，不能直接將數組列與運算子組合使用。

假設要使用SQL查詢col_long_array數組列的資料，資料表中該列為字串類型，多元索引中該列類型為Long類型數組，查詢條件為數組元素值等於1。SQL樣本如下：

SELECT * FROM `test_table__test_table_index` WHERE ARRAY_EXTRACT(col_long_array) = 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（字串類型）兩列。

建立一個多元索引。具體操作，請參見使用控制台建立多元索引或使用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%';
```
返回結果如下圖所示。