検索インデックス内の配列型のフィールドを照会するためのSQLステートメントの実行 - Tablestore

検索インデックスは、Long、Double、Boolean、Keyword、Textなどの基本型のフィールドに加えて、配列型のフィールドもサポートしています。配列型のフィールドを使用して、基本型の要素を格納できます。 SELECTステートメントのWHERE句にARRAY_EXTRACT条件を指定すると、SELECTステートメントを実行して検索インデックス内の配列型のフィールドを照会できます。このトピックでは、検索インデックス内の配列型のフィールドを照会するためのSQLステートメントの実行方法について説明します。

説明

配列型の詳細については、ARRAYを参照してください。

使用上の注意

検索インデックス内のGeo-point型のフィールドを照会するためにSQLステートメントを実行することはできません。
データテーブル内でフィールドがマッピングされている列がString型の場合にのみ、検索インデックス内のフィールドの型としてArrayを指定できます。検索インデックスを作成する際には、フィールドの型を配列を使用して格納する要素の実際の型に設定し、フィールドの配列属性を有効にする必要があります。

データ型マッピング

データテーブルのデータ型	検索インデックスのデータ型	SQLのデータ型
String	フィールドの型は、配列を使用して格納する要素の実際の型と同じです。Tablestoreコンソールでフィールドの配列がオンになっているか、Tablestore SDKを使用してフィールドのIsArrayパラメータがtrueに設定されています。	VARCHAR（プライマリキー） MEDIUMTEXT（事前定義列）

前提条件

データテーブルが作成され、データがデータテーブルに書き込まれています。詳細については、テーブルの操作およびデータの書き込みを参照してください。

手順

SQLステートメントを実行して配列型のフィールドを照会するには、データテーブルの検索インデックスを作成し、検索インデックスのマッピングテーブルを作成してから、SQLステートメントを実行して検索インデックス内の配列型のフィールドを照会する必要があります。

ステップ1：検索インデックスを作成する

配列型のフィールドを使用して要素を格納するには、検索インデックスを作成するときに、Tablestoreコンソールでフィールドの「配列」を有効にするか、Tablestore SDKを使用してフィールドのIsArrayパラメーターをtrueに設定する必要があります。詳細については、検索インデックスの作成を参照してください。

ステップ2：検索インデックスのマッピングテーブルを作成する

SQLステートメントを実行して検索インデックス内の配列型のフィールドを照会するには、検索インデックスのマッピングテーブルを作成する必要があります。検索インデックスのマッピングテーブルの作成方法の詳細については、検索インデックスのマッピングテーブルの作成を参照してください。

CREATE TABLEステートメントでは、配列型の各フィールドに有効な名前とSQLでマッピングされたデータ型を指定する必要があります。検索インデックスのマッピングテーブルを作成する場合は、配列型のフィールドの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"}';

ステップ3：SQLステートメントを実行して検索インデックスを使用してデータを照会する

SQLステートメントを実行して配列型のフィールドを照会するには、ARRAY_EXTRACT(col_name)関数を使用する必要があります。この関数では、col_nameは配列型のフィールドの名前を指定します。この関数は、演算子とともにクエリ条件として使用できます。例：ARRAY_EXTRACT(col_long_array) = 1。

重要

SQLステートメントを実行して配列型のフィールドを照会する場合、演算子とともに配列型のフィールドを使用することはできません。

たとえば、検索インデックスにcol_long_arrayという名前の配列型のフィールドが含まれており、データテーブル内でフィールドがマッピングされている列の型がStringで、配列型のフィールドにLong型の要素が格納されているとします。次のSQLステートメントの例を実行して、配列型のcol_long_arrayフィールドに値が1の要素が含まれている行を照会できます。

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

制限事項

検索インデックス用に作成されたマッピングテーブルを使用してSQLステートメントを実行する場合にのみ、ARRAY_EXTRACT(col_name)関数を条件として使用して配列型のフィールドを照会できます。関数には配列型のフィールドを1つだけ指定できます。この関数はSELECTステートメントのWHERE句として使用できますが、SELECTステートメントの列式、集計関数、またはソートには使用できません。
配列型のフィールドはSELECTステートメントのフィールドまたは列式として使用できますが、集計関数またはソートには使用できません。
ARRAY_EXTRACT(col_name)関数を演算子とともにクエリ条件として使用する場合は、配列型のフィールド内の要素の型が変換された後に計算を実行することはできません。配列型のフィールド内の要素の型が有効であることを確認してください。たとえば、col_long_arrayフィールドは配列型で、Long型の要素を格納します。array_extract(col_long_array) = 1をクエリ条件として使用して計算を実行できます。ただし、array_extract(col_long_array) = '1'をクエリ条件として使用して計算を実行することはできません。
配列型のフィールド内の要素の型がTextの場合、ARRAY_EXTRACT(col_name)関数を全文検索で提供されるtext_match()関数またはtext_match_phrase()関数とともに使用する必要があります。例：text_match(array_extract(col_text), "xxx")。全文検索の詳細については、全文検索を参照してください。

例

この例では、test_tableという名前のデータテーブルを使用します。データテーブルには、String型のcol_keyword_array列とString型のcol_long_array列が含まれています。

検索インデックスを作成します。詳細については、Tablestoreコンソールの使用およびTablestore SDKの使用を参照してください。
この例では、test_table_indexという名前の検索インデックスが作成されます。検索インデックスには、2つの配列型のフィールドが含まれています。Keyword型の要素を格納するcol_keyword_arrayフィールドとLong型の要素を格納するcol_long_arrayフィールドです。
次の図は、Tablestoreコンソールで検索インデックスを作成する方法を示しています。
検索インデックスのマッピングテーブルを作成します。詳細については、検索インデックスのマッピングテーブルの作成を参照してください。
この例では、検索インデックス用にtest_table__test_table_indexという名前のマッピングテーブルが作成されます。マッピングテーブルでは、col_keyword_arrayフィールドとcol_long_arrayフィールドの型は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ステートメントを実行してデータを照会します。
この例では、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%';
```
次の図は、クエリ結果を示しています。