如何通過SQL查詢查詢多元索引巢狀型別的資料 - Tablestore

多元索引除了提供Long、Double、Boolean、Keyword、Text等基本類型外，還提供了巢狀型別（Nested）。巢狀型別代表嵌套文件類型。嵌套文檔是指對於一行資料（文檔）可以包含多個子行（子文檔），多個子行儲存在一個巢狀型別欄位中。本文介紹如何通過SQL查詢功能查詢巢狀型別的列資料。

說明

關於巢狀型別的更多資訊，請參見巢狀型別。

注意事項

要使用巢狀型別，在資料表中的列資料類型必須為字串。建立多元索引時，該列的資料類型需要設定為巢狀型別，並且正確填寫其子列的資料與資料類型。

資料類型映射

資料表中資料類型	多元索引中資料類型	SQL資料類型
字串	巢狀型別，子列資料類型和實際寫入的資料類型一致。	VARCHAR（主鍵） MEDIUMTEXT（預定義列）

建立方法

如果要在SQL查詢時使用巢狀型別，則必須建立多元索引映射關係。關於建立多元索引映射關係的具體操作，請參見建立多元索引的映射關係。

在CREATE TABLE語句中巢狀型別的列需要正確設定嵌套列名和對應的SQL資料類型即可，巢狀型別列的內部子列會自動建立。在多元索引映射關係時，推薦定義巢狀型別的列為MEDIUMTEXT類型。

說明

使用ALTER TABLE語句添加或刪除巢狀型別列時，巢狀型別列的內部子列也會被自動添加或刪除。

建立包含巢狀型別列的多元索引映射關係，SQL樣本如下：

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

通過SQL查詢資料

通過SQL查詢巢狀型別時，巢狀型別列的子列支援直接與運算子組合使用（例如`col_nested.col_long` = 1）或者使用NESTED_QUERY(subcol_column_condition)函數實現，請實際需要查詢需求選擇。其中subcol_column_condition為同一嵌套層級下的子列查詢條件。

當查詢條件中使用巢狀型別的列時，巢狀型別列下的子列列名格式為`嵌套列名.子列名`。如果巢狀型別為多層嵌套，則繼續向後添加子列名即可，使用半形句號（.）進行串連。例如巢狀型別列名為col_nested，子列名為col_long，則該子列作為查詢條件時設定為`col_nested.col_long`；假如巢狀型別列名為col1，該列有一個巢狀型別子列col2，col2列有一個子列col3，則col3子列作為查詢條件時設定為`col1.col2.col3`。

當使用NESTED_QUERY(subcol_column_condition)函數作為查詢條件時，該行同一個嵌套的JSON元素必須同時滿足全部查詢條件。

假設巢狀型別的列名為tags，其元素只有一行包括兩個JSON元素，具體值為[{"tagName":"tag1", "score":0.8}, {"tagName":"tag2", "score":0.2}]，使用不同方式查詢巢狀型別的列資料時的區別如下：

巢狀型別的子列支援直接與運算子組合使用
SQL樣本如下：
```
SELECT tags FROM `test_table__test_table_index` WHERE `tags.tagName` = 'tag1' AND `tags.score` = 0.2;
```
該行的第一個JSON元素滿足`tags.tagName` = 'tag1'，第二個JSON元素滿足`tags.score` = 0.2，因此返回結果如下：
```
[{"tagName":"tag1", "score":0.8}, {"tagName":"tag2", "score":0.2}]
```
使用NESTED_QUERY(subcol_column_condition)函數實現
SQL樣本如下：
```
SELECT tags FROM `test_table__test_table_index` WHERE NESTED_QUERY(`tags.tagName` = 'tag1' AND `tags.score` = 0.2);
```
由於沒有任何一個JSON元素同時滿足`tags.tagName` = 'tag1'和`tags.score` = 0.2，因此返回結果為空白。

使用限制

NESTED_QUERY(subcol_column_condition)用於同一層級下的巢狀查詢，並且需要該行同一個嵌套的JSON元素同時滿足全部查詢條件。SQL樣本如下：
```
SELECT * FROM `test_table__test_table_index` WHERE NESTED_QUERY(`col1.col2` = 1 AND NESTED_QUERY(`col1.col3.col4` = 2));
```
巢狀型別子列直接與運算子組合使用或者使用NESTED_QUERY(subcol_column_condition)函數作為巢狀查詢條件時只能在多元索引映射關係上使用。
NESTED_QUERY(subcol_column_condition)函數只能設定一個運算式類型的參數，多個運算式之間需要使用邏輯運算子（AND或OR）進行串連。該函數只能作為SELECT語句的WHERE子句，不能作為SELECT語句的列運算式，不能用於彙總函式計算，不能進行分組和排序。
巢狀型別列下子列不能作為SELECT語句的列名或者列運算式，且不能用於彙總函式計算，不能進行分組和排序。
使用ALTER TABLE語句添加或刪除列時，不能直接添加或刪除巢狀型別列下子列，只能添加或刪除存在於資料表中的巢狀型別列，子列會被自動添加或刪除。
嵌套子列不能進行資料類型轉換後的計算，也不能對無法下推到多元索引的函數進行計算。使用時請確保嵌套子列對應的資料類型正確。

使用樣本

假設資料表名稱為test_table，該表中有col_nested（字串類型）列。

如果要使用SQL語句查詢巢狀型別的資料，則需要為資料表建立多元索引並建立多元索引的映射關係，然後使用SQL語句查詢巢狀型別列的資料。具體步驟如下：

建立一個多元索引。具體操作，請參見使用控制台建立多元索引或使用SDK建立多元索引。
多元索引名稱為test_table_index，該多元索引包括col_nested（巢狀型別）列。
使用控制台建立多元索引的配置如下圖所示。
建立多元索引映射關係。更多資訊，請參見建立多元索引的映射關係。
多元索引映射關係名稱為test_table__test_table_index，該映射關係中col_nested列對應的SQL資料類型為MEDIUMTEXT。
SQL樣本如下：
```
CREATE TABLE `test_table__test_table_index`(
    `col_nested` MEDIUMTEXT
) 
ENGINE='searchindex'
ENGINE_ATTRIBUTE='{"index_name":"test_table_index", "table_name":"test_table"}';
```
建立多元索引映射關係後，請根據需要執行如下操作：
- 查詢表的描述資訊。更多資訊，請參見查詢表的描述資訊。
  執行如下語句查詢test_table__test_table_index的描述資訊。
```
DESCRIBE `test_table__test_table_index`;
```
  返回結果如下圖所示。嵌套列col_nested下子列name和age均已自動建立，列名分別為col_nested.name和col_nested.age。
- 擷取表中資料。
  執行如下語句擷取表中資料。
```
SELECT * FROM `test_table__test_table_index`;
```
  返回結果如下圖所示。假設多元索引映射關係test_table__test_table_index有5條資料。
使用SELECT語句查詢資料。
- 樣本一：查詢col_long_array巢狀型別列下子列age大於30的行。
```
SELECT * FROM `test_table__test_table_index` WHERE `col_nest.age` >30;
```
  返回結果如下圖所示。
- 樣本二：查詢col_long_array巢狀型別列下的JSON元素滿足name以字母I開頭，並且子列age小於20的行。
```
SELECT * FROM `test_table__test_table_index` WHERE `col_nested.name` like 'I%' AND `col_nested.age` < 20;
```
  返回結果如下圖所示。
- 樣本三：查詢col_long_array巢狀型別列的某個JSON元素同時滿足name以字母I開頭，並且age小於20。
  此功能需要使用NESTED_QUERY(subcol_column_condition)函數來實現。
```
SELECT * FROM `test_table__test_table_index` WHERE NESTED_QUERY(`col_nested.name` like 'I%' AND `col_nested.age` < 20);
```
  返回結果如下圖所示。