Tablestore:Search index operations

サポートされる操作

全文検索

Text 型のフィールドのデータに対して、`TEXT_MATCH` を使用してトークン一致を、`TEXT_MATCH_PHRASE` を使用してフレーズ一致を実行します。

TEXT_MATCH(fieldName, text [, options])

TEXT_MATCH_PHRASE(fieldName, text)

例

content 列で "tablestore" トークンを含むデータをクエリします。

SELECT * FROM search_exampletable WHERE TEXT_MATCH(content, 'tablestore') LIMIT 10;

content 列で "sql query" を連続したフレーズとして含むデータをクエリします。

SELECT * FROM search_exampletable WHERE TEXT_MATCH_PHRASE(content, 'sql query') LIMIT 10;

options パラメーターを使用して、少なくとも 2 つのトークンを含むデータに一致させます。

SELECT * FROM search_exampletable WHERE TEXT_MATCH(content, 'tablestore is cool', 'or', '2') LIMIT 10;

AND 演算子を使用して、すべてのトークンが存在することを要求します。

SELECT * FROM search_exampletable WHERE TEXT_MATCH(content, 'tablestore is cool', 'and') LIMIT 10;

配列クエリ

ARRAY_EXTRACT 関数を使用して、配列型の列のデータをクエリします。コンソールで配列オプションを有効にするか、SDK で IsArray を true に設定して、多次元インデックスで列を配列型として設定します。データを書き込むとき、配列の値は ["a","b","c"] のような JSON 配列フォーマットである必要があります。

データ型のマッピング

ARRAY_EXTRACT(col_name)

`ARRAY_EXTRACT` は配列の列を展開し、`WHERE` 句の条件として演算子と組み合わせます。サポートされている演算子には、等価 (=)、範囲 (>, <)、および `LIKE` が含まれます。

制限事項

• `ARRAY_EXTRACT` は多次元インデックスのマッピングテーブルでのみ使用でき、呼び出しごとに 1 つの配列列パラメーターのみが許可されます。この関数は `WHERE` 句の条件としてのみ使用できます。`SELECT` 式や集約、ソートには使用できません。

• `ARRAY_EXTRACT` を使用しない配列の列は、`SELECT` の列名や式として使用できますが、集約やソートには使用できません。

• ARRAY_EXTRACT をクエリ条件として演算子と組み合わせる場合、データ型の変換はサポートされていません。 クエリ値は、配列カラムのデータ型と一致する必要があります。 たとえば、Long 型の配列カラムは ARRAY_EXTRACT(col) = 1 をサポートしますが、ARRAY_EXTRACT(col) = '1' はサポートしていません。

• テキスト型の配列要素は、TEXT_MATCH(ARRAY_EXTRACT(col_text), 'keyword') のように、TEXT_MATCH 関数または TEXT_MATCH_PHRASE 関数と共に使用する必要があります。

例

-- 配列に 'apple' という値を含む行をクエリする
SELECT * FROM search_exampletable WHERE ARRAY_EXTRACT(col_array) = 'apple';

-- 配列に 'd' で始まる要素を含む行をクエリする
SELECT * FROM search_exampletable WHERE ARRAY_EXTRACT(col_array) LIKE 'd%';

ネスト型クエリ

ネスト型の列は、各要素が複数のサブ列を含む JSON 配列を格納します。データテーブル内の列のデータ型は String である必要があります。多次元インデックスを作成する際に、列をネスト型として設定し、サブ列のデータ型を指定します。

マッピングテーブルを作成する際、ネスト型の列を MEDIUMTEXT として定義します。内部サブ列は自動的に作成され、DESCRIBE で col_nested.name や col_nested.age のように確認できます。クエリでは、サブ列名は nested_column.sub_column というフォーマットを使用し、col1.col2.col3 のようにピリオド (.) で複数のネストレベルを区切ります。

データ型のマッピング

クエリメソッド

SELECT * FROM search_exampletable WHERE `col_nested.age` > 30;

NESTED_QUERY(subcol_column_condition)

2 つのメソッドの違い

ネストされた列 tags に次の行データが含まれていると仮定します：[{"tagName":"tag1", "score":0.8}, {"tagName":"tag2", "score":0.2}]

• tags.tagName — 一致します。最初の要素が tagName 条件を満たし、2 番目の要素が score 条件を満たすためです。

• NESTED_QUERY( — 一致しません。単一の要素で両方の条件を満たすものがないためです。

制限事項

• `NESTED_QUERY` は多次元インデックスのマッピングテーブルでのみ使用でき、`WHERE` 句としてのみ使用できます。`SELECT` 式や集約、グループ化、ソートには使用できません。

• ネストされたサブ列は、`SELECT` 式や集約、グループ化、ソートには使用できません。

• `ALTER TABLE` はネストされたサブ列を直接追加または削除できません。ネストされた列全体を追加または削除することしかできず、サブ列はそれに伴って自動的に追加または削除されます。

• ネストされたサブ列は、データ型変換や、多次元インデックスにプッシュダウンできない関数計算をサポートしていません。ネストされたサブ列のデータ型が正しいことを確認してください。

例

-- サブ列の直接クエリ：ネストされた列で age > 30 の行をクエリする
SELECT * FROM search_exampletable WHERE `col_nested.age` > 30;

-- NESTED_QUERY：単一の要素で name が 'I' で始まり、age < 20 の行をクエリする
SELECT * FROM search_exampletable WHERE NESTED_QUERY(`col_nested.name` LIKE 'I%' AND `col_nested.age` < 20);

-- 複数レベルのネスト
SELECT * FROM search_exampletable WHERE NESTED_QUERY(`col1.col2` = 1 AND NESTED_QUERY(`col1.col3.col4` = 2));

仮想列クエリ

検索インデックス仮想カラムにより、データテーブルのストレージ構造を変更することなく、検索インデックススキーマを変更して、新しいフィールドと型をクエリできます。仮想カラムは、実際の SQL データの型でマッピングテーブルに定義されます。

データ型のマッピング

サポートされる使用方法

• `WHERE` 句でのデータフィルタリング。条件内の仮想列のデータ型は、クエリパラメーターの型と一致する必要があります。

• 集約とグループ化での使用。仮想列のソースデータ型は、操作と互換性がある必要があります。例えば、`SUM` は Long 型と Double 型のみをサポートします。Keyword 型の仮想列は合計できず、Text 型の仮想列はグループ化をサポートしません。

• TopN クエリとソートがサポートされています。ソートには `LIMIT` が必要です。

制限事項

• 仮想列は、検索インデックスマッピングテーブルでのみ使用できます。

• 仮想カラムはクエリ条件でのみ使用できます。 SELECT でカラム値を返すために使用することはできません。 値を返すには、仮想カラムのソースカラムを指定します。 SELECT * は影響を受けず、結果から仮想カラムを自動的に除外します。

• 仮想列は、列の比較、計算、または `JOIN` には使用できません。

• 仮想列は、データ型変換や、多次元インデックスにプッシュダウンできない関数計算をサポートしていません。現在、SQL クエリでは集計関数のみがプッシュダウンできます。

例

仮想カラムを含む検索インデックスマッピングテーブルを作成します:

CREATE TABLE search_exampletable(
    col_keyword MEDIUMTEXT,
    col_keyword_virtual_long BIGINT
)
ENGINE='searchindex',
ENGINE_ATTRIBUTE='{"index_name":"exampletable_index","table_name":"exampletable"}';

仮想列を使用してクエリを実行します。

SELECT * FROM search_exampletable WHERE col_keyword_virtual_long > 100 LIMIT 10;

ベクトル検索

`VECTOR_QUERY_FLOAT32` 関数を使用して、近似最近傍 (ANN) クエリを実行します。ベクトルフィールドはデータテーブルに文字列として格納されます。多次元インデックスでそれらをベクトル型として設定し、ディメンション、データ型、および距離メトリックを指定します。マッピングテーブルにおけるベクトル列の SQL データ型は MEDIUMTEXT です。

VECTOR_QUERY_FLOAT32

VECTOR_QUERY_FLOAT32(fieldName, float32QueryVector, topK, filter)

SCORE() 関数

`VECTOR_QUERY_FLOAT32` と共に `SCORE()` を `SELECT` 式として使用すると、各結果の関連性スコアが返されます。スコアが高いほど、類似性が高いことを示します。

SCORE()

制限事項

• `VECTOR_QUERY_FLOAT32` は多次元インデックスのマッピングテーブルでのみ使用でき、`LIMIT` と共に使用する必要があります。`HAVING` 句はサポートされていません。

• `VECTOR_QUERY_FLOAT32` は `WHERE` 句としてのみ使用できます。`SELECT` 式や集約、グループ化、ソートには使用できません。

• `SCORE()` は `VECTOR_QUERY_FLOAT32` と共に `SELECT` 式としてのみ使用できます。`WHERE` 句、集約、ソートには使用できません。

• `WHERE` 句の他の条件は、多次元インデックスへのプッシュダウンをサポートしている必要があります。そうでない場合、クエリは失敗します。サポートされているプッシュダウン演算子については、「クエリの最適化」をご参照ください。

例

col_vector 列で、指定されたベクトルに最も類似する 10 件の結果をクエリします。

SELECT *, SCORE() FROM exampletable WHERE VECTOR_QUERY_FLOAT32(col_vector, '[1.5, -1.5, 2.5, -2.5]', 10) LIMIT 10;

フィルターを使用してベクトル検索の前にサンプル候補セットを絞り込み、より正確な結果を得ます。

SELECT *, SCORE() FROM exampletable WHERE VECTOR_QUERY_FLOAT32(col_vector, '[1.5, -1.5, 2.5, -2.5]', 100, col_keyword='cat_a' AND year_num=2024) LIMIT 10;

ベクトル検索後のフィルタリングのために `WHERE` 句で `AND` を使用します。topK の結果には、すべての一致する行が含まれない場合があります。

SELECT *, SCORE() FROM exampletable WHERE col_keyword='cat_a' AND VECTOR_QUERY_FLOAT32(col_vector, '[1.5, -1.5, 2.5, -2.5]', 500) LIMIT 10;

JSON 関数

Tablestore SQL の JSON 関数は、MySQL 5.7 の構文に準拠しており、JSON 形式の列からデータを抽出します。

->> (JSON パス抽出)

JSON 列から指定されたパスの値を抽出し、引用符を解除して文字列に変換します。 JSON_UNQUOTE(JSON_EXTRACT()) に相当します。

column->>'$.path'

例

SELECT col_json->>'$.city' AS city FROM exampletable LIMIT 10;

JSON_UNQUOTE

JSON 値の外側の引用符を削除し、文字列を返します。引数が NULL の場合は NULL を返します。

JSON_UNQUOTE(json_val)

例

SELECT JSON_UNQUOTE(JSON_EXTRACT(col_json, '$.city')) AS city FROM exampletable LIMIT 10;

JSON_EXTRACT

JSON 列から指定されたパスにあるサブドキュメントを抽出します。戻り値は JSON フォーマットを保持し、文字列値は引用符で囲まれます。複数のパスを指定でき、結果は配列フォーマットで返されます。

JSON_EXTRACT(json_doc, path[, path] ...)

例

単一パスの抽出:

SELECT JSON_UNQUOTE(JSON_EXTRACT(col_json, '$.city')) AS city FROM exampletable WHERE pk = 1;

複数のパスを抽出します。結果は配列フォーマットで返されます：

-- col_json に {"a": 1, "b": 2, "c": {"d": 4}} が含まれているとします。
SELECT JSON_UNQUOTE(JSON_EXTRACT(col_json, '$.a', '$.b', '$.c.d')) AS subdoc FROM exampletable WHERE pk = 1;
-- 結果: [1, 2, 4]

JSON Path 構文

パスは、JSON ドキュメント全体を表す $ で始まる必要があります。$ の後にパスセレクターを追加します。セレクターは組み合わせることができます。

JSON オブジェクトのクエリ例

JSON カラムに {"a": 1, "f": [1, 2, 3], "c": {"d": 4}} が含まれているとします:

JSON 配列クエリの例

JSON カラムに [3, {"a": [5, 6], "b": 10}, [99, 100]] が含まれていると仮定します。非スカラーの戻り値は、ネストされたクエリをサポートします。