Lindorm の JSON データ型における検索インデックスのユーザーガイド - Lindorm

Lindorm は、ワイドテーブルの JSON 列に検索インデックスを作成することをサポートしています。利用可能なクエリ関数は、JSON データの構造によって異なります。このガイドでは、サポートされている 3 つの JSON パターン (基本要素の配列、JSON オブジェクト、オブジェクトの配列) をすべて取り上げ、それぞれに適用されるインデックスタイプとクエリ関数を説明します。

前提条件

開始する前に、以下を確認してください：

LindormTable エンジンがバージョン 2.8.5 以降であること
Lindorm LTS エンジンがバージョン 3.8.13.3 以降であること

エンジンバージョンの確認またはアップグレードについては、「マイナーバージョンの更新」をご参照ください。

注意事項

JSON の自動型推論に依存する場合、JSON フィールドのデータ型が書き込みごとに一致しない場合 (たとえば、フィールド a が最初に文字列として書き込まれ、後で数値として書き込まれた場合)、最初の書き込みから推論された型が使用されます。後続の適合しない値はスキップされます。
JSON ドキュメントに多くの内部フィールドが含まれている場合、自動推論が検索インデックスの列数制限に達し、インデックス同期タスクが失敗する可能性があります。実際にクエリする JSON フィールドのみにインデックスを作成することを推奨します (詳細は以下の例をご参照ください)。デフォルトの列数制限は 1000 です。LindormTable 2.8.6.1 以降では、次の SQL で制限を調整できます：
```
ALTER INDEX idx ON search_table SET SEARCH_INDEX_MAX_COLUMN_COUNT='2000';
```

適切なインデックスタイプの選択

JSON 列に検索インデックスを作成する前に、データが 3 つのパターンのうちどれに従っているかを特定してください。単一の列でパターンを混在させると、インデックス作成中に無効なデータが警告なくスキップされます。

JSON パターン	例	インデックスタイプ	サポートされるクエリ関数
基本要素の配列	`[1, 2, 3]` または `["a", "b"]`	`keyword`、`integer` など) を指定した `mapping`	`JSON_CONTAINS`、`JSON_CONTAINS_ANY`
JSON オブジェクト	`{"name": "アリス", "age": 13}`	`type=jsonobject` または `mapping` で `"type": "object"`	`JSON_EXTRACT`、`JSON_EXTRACT_STRING`、`JSON_CONTAINS`、`JSON_CONTAINS_ANY` (ネストされた配列に対して)、`MATCH`...`AGAINST`
オブジェクトの配列	`[{"name": "アリス"}, {"name": "ボブ"}]`	`type=jsonarray` または `mapping` で `"type": "nested"`	`SEARCH_QUERY` と Elasticsearch DSL のみ

重要

単一の列で 3 つのパターンを混在させないでください。無効なデータはスキップされ、インデックスが作成されない場合があります。

基本要素の配列

基本要素の配列は、["101", "102", "109"] (文字列配列) や [1, 2, 3] (整数配列) のように、トップレベルにスカラー値のフラットなリストを格納します。

ワイドテーブルの作成

CREATE TABLE test_json_array(id VARCHAR, c1 JSON, c2 JSON, PRIMARY KEY (id));

データの挿入

UPSERT INTO test_json_array(id, c1, c2) VALUES ('1001', '["101", "102", "109"]', '[1, 2, 3]');
UPSERT INTO test_json_array(id, c1, c2) VALUES ('1002', '["999", "888", "777"]', '[1, 2, 3, 4, 5]');

検索インデックスの作成

mapping パラメーターで要素の型を指定します。文字列配列には keyword を、整数配列には integer を使用します。

CREATE INDEX idx USING SEARCH ON test_json_array(
  c1(mapping='{
    "type": "keyword"
  }'),
  c2(mapping='{
    "type": "integer"
  }')
);

クエリ

利用可能な関数は 2 つあります：

JSON_CONTAINS：配列に指定された値がすべて存在する場合に行を返します
JSON_CONTAINS_ANY：指定された値の少なくとも 1 つが存在する場合に行を返します

`JSON_CONTAINS` — 指定されたすべての値を含む行

-- どの行にも "101" と "999" の両方が含まれていないため、結果は空になります
Lindorm> SELECT * FROM test_json_array WHERE JSON_CONTAINS(c1, '["101", "999"]');
Empty set (0.11 sec)

-- id=1001 には "101" と "102" の両方が含まれています
Lindorm> SELECT * FROM test_json_array WHERE JSON_CONTAINS(c1, '["101", "102"]');
+------+-----------------------+-----------+
| id   | c1                    | c2        |
+------+-----------------------+-----------+
| 1001 | ["101", "102", "109"] | [1, 2, 3] |
+------+-----------------------+-----------+
1 row in set (0.01 sec)

`JSON_CONTAINS_ANY` — 指定された値の少なくとも 1 つを含む行

-- id=1001 には "101" が、id=1002 には "999" が含まれているため、両方の行が返されます
Lindorm> SELECT * FROM test_json_array WHERE JSON_CONTAINS_ANY(c1, '["101", "999"]');
+------+-----------------------+---------------------+
| id   | c1                    | c2                  |
+------+-----------------------+---------------------+
| 1002 | ["999", "888", "777"] | [1, 2, 3, 5, 9, 10] |
| 1001 | ["101", "102", "109"] | [1, 2, 3]           |
+------+-----------------------+---------------------+
2 rows in set (0.01 sec)

JSON オブジェクト

JSON オブジェクトは、{"name": "アリス", "age": 13, "hobbies": ["読書", "バドミントン"]} のように、トップレベルに構造化ドキュメントを格納します。フィールドには、スカラー値、配列、またはネストされたオブジェクトを含めることができます。

ワイドテーブルの作成

CREATE TABLE test_json_object(id VARCHAR, user_info JSON, PRIMARY KEY (id));

データの挿入

UPSERT INTO test_json_object(id, user_info) VALUES ('1001', '{"name": "アリス", "age": 13, "address": "杭州、浙江省", "hobbies": ["ゲーム", "読書", "バドミントン"]}');
UPSERT INTO test_json_object(id, user_info) VALUES ('1002', '{"name": "ボブ", "age": 9, "address": "寧波、浙江省", "hobbies": ["ゲーム"]}');
UPSERT INTO test_json_object(id, user_info) VALUES ('1003', '{"name": "ジョン", "age": 21, "address": "深セン、広東省", "hobbies": ["読書", "バドミントン", "食事"]}');

検索インデックスの作成

2 つのアプローチが利用可能です。可能な限り、事前定義された構造を使用してください。

自動型推論

type=jsonobject を指定すると、システムは最初に書き込まれた値からフィールドタイプを推論します。たとえば、user_info.name は文字列として、user_info.age は数値として推論されます。後で書き込まれたデータが推論された型と一致しない場合、インデックスの一貫性を保つためにスキップされます。

CREATE INDEX idx USING SEARCH ON test_json_object(user_info(type=jsonobject));

フィールド構造の事前定義 (推奨)

自動型推論には 2 つの制限があります：

フィールドに対してトークン化 (全文) クエリを実行するには、フィールドタイプを明示的に text に設定する必要があります。デフォルトで推論される型は keyword であり、これは形態素解析をサポートしていません。
JSON オブジェクトにネストされたオブジェクトの配列が含まれている場合、フィールドタイプを明示的に nested に設定する必要があります。

内部フィールド構造を定義するには mapping を使用します。フィールドタイプは Elasticsearch の構文に従います。

-- トークン化クエリを有効にし、インデックス作成の動作を制御するために、フィールドタイプを明示的に定義します
CREATE INDEX idx USING SEARCH ON test_json_object(
  user_info(mapping='{
    "type": "object",
    "properties": {
      "name": {
        "type": "keyword"
      },
      "age": {
        "type": "integer"
      },
      "address": {
        "type": "text",
        "analyzer": "ik_max_word"
      },
      "hobbies": {
        "type": "keyword"
      }
    }
  }')
);

フィールドのサブセットのみにインデックスを作成し、残りを無視するには、"dynamic": "false" を追加します：

CREATE INDEX idx USING SEARCH ON test_json_object(
  user_info(mapping='{
    "type": "object",
    "dynamic": "false",
    "properties": {
      "name": {
        "type": "keyword"
      },
      "age": {
        "type": "integer"
      },
      "address": {
        "type": "text",
        "analyzer": "ik_max_word"
      },
      "hobbies": {
        "type": "keyword"
      }
    }
  }')
);

クエリ

JSON オブジェクトでは 3 つのクエリメソッドがサポートされています：

JSON_EXTRACT / JSON_EXTRACT_STRING — パスによって単一のスカラーフィールドを照合します
JSON_CONTAINS / JSON_CONTAINS_ANY — オブジェクト内の配列フィールドの要素を照合します
MATCH...AGAINST と JSON_EXTRACT の組み合わせ — text フィールドでの全文 (トークン化) 検索

サブオブジェクト全体の照合はサポートされていません。たとえば、WHERE JSON_EXTRACT(json_col, '$.user') = '{"name": "アリス", "age": 12}' は無効です。検索インデックスにおける JSON_EXTRACT は、単一のスカラー要素のみを照合します。

`JSON_EXTRACT` — スカラーフィールド値による行の検索

-- 名前がアリスであるユーザーを検索
Lindorm> SELECT * FROM test_json_object WHERE JSON_EXTRACT_STRING(user_info, '$.name')='Alice';
+------+------------------------------------------------------------------------------------------------+
| id   | user_info                                                                                      |
+------+------------------------------------------------------------------------------------------------+
| 1001 | {"name": "Alice", "age": 13, "address": "Hangzhou, Zhejiang Province", "hobbies": ["play games", "read", "badminton"]} |
+------+------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

-- 年齢が 21 歳のユーザーを検索
Lindorm> SELECT * FROM test_json_object WHERE JSON_EXTRACT(user_info, '$.age')=21;
+------+--------------------------------------------------------------------------------------------------------+
| id   | user_info                                                                                              |
+------+--------------------------------------------------------------------------------------------------------+
| 1003 | {"name": "John", "age": 21, "address": "Shenzhen, Guangdong Province", "hobbies": ["read", "badminton", "food"]}       |
+------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

`MATCH`...`AGAINST` と `JSON_EXTRACT` の組み合わせ — テキストフィールドでの全文検索

address フィールドは、type: text として事前定義されている必要があります。デフォルトで推論される型 (keyword) は、トークン化クエリをサポートしていません。

-- 住所に "Zhejiang" が含まれるユーザーを検索 (複数単語にまたがるトークン化された一致)
Lindorm> SELECT * FROM test_json_object WHERE MATCH(JSON_EXTRACT(user_info, '$.address')) AGAINST ('Zhejiang');
+------+---------------------------------------------------------------+
| id   | user_info                                                     |
+------+---------------------------------------------------------------+
| 1001 | {"name": "Alice", "age": 13, "address": "Hangzhou, Zhejiang", "hobbies": ["play games", "read", "badminton"]}    |
| 1002 | {"name": "Bob", "age": 9, "address": "Ningbo, Zhejiang", "hobbies": ["play games"]}                              |
+------+---------------------------------------------------------------+
2 rows in set (0.03 sec)

-- 住所に "Hangzhou" が含まれるユーザーを検索
Lindorm> SELECT * FROM test_json_object WHERE MATCH(JSON_EXTRACT(user_info, '$.address')) AGAINST ('Hangzhou');
+------+---------------------------------------------------------------+
| id   | user_info                                                     |
+------+---------------------------------------------------------------+
| 1001 | {"name": "Alice", "age": 13, "address": "Hangzhou, Zhejiang", "hobbies": ["play games", "read", "badminton"]}    |
+------+---------------------------------------------------------------+
1 row in set (0.01 sec)

`JSON_CONTAINS` — 配列フィールドが特定の値を含む行の検索

-- 趣味のリストに "読書" が含まれるユーザーを検索
Lindorm> SELECT * FROM test_json_object WHERE JSON_CONTAINS(user_info, '["read"]', '$.hobbies');
+------+---------------------------------------------------------------------------------------------------------------+
| id   | user_info                                                                                                     |
+------+---------------------------------------------------------------------------------------------------------------+
| 1003 | {"name": "John", "age": 21, "address": "Shenzhen, Guangdong Province", "hobbies": ["read", "badminton", "food"]}              |
| 1001 | {"name": "Alice", "age": 13, "address": "Hangzhou, Zhejiang Province", "hobbies": ["play games", "read", "badminton"]}       |
+------+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.04 sec)

オブジェクトの配列

オブジェクトの配列は、[{"name": "アリス", "age": 12}, {"name": "ボブ", "age": 20}] のように、各要素が JSON オブジェクトであるトップレベルの配列を格納します。

オブジェクトの配列に異なるインデックスタイプが必要な理由

特殊なインデックスタイプがない場合、データベースはオブジェクトの配列を独立した複数値フィールドにフラット化し、同じオブジェクトに属するフィールド間の関連付けが失われます。たとえば、次の 2 つの行があるとします：

id	user
1002	`[{"name": "アリス", "age": 9}, {"name": "ボブ", "age": 20}]`

単純な (ネストされていない) インデックスは、name と age の値を name = [アリス, ボブ] および age = [9, 20] のようなフラットなリストとして格納します。「年齢が 10 歳以上のアリス」というクエリは、アリスの実際の年齢が 9 歳であるにもかかわらず、フラット化されたフィールドにアリスと age=20 が共存しているため、この行に誤って一致してしまいます。

Lindorm は、オブジェクトの配列のインデックスに対して内部的に type: nested を使用します。配列内の各オブジェクトは分離されたユニットとしてインデックス付けされるため、同じオブジェクト内のフィールドの関連付けが保持されます。その結果、このパターンのクエリには、埋め込み Elasticsearch DSL を使用した SEARCH_QUERY のみがサポートされます。DSL の nested クエリは、これらのオブジェクト内の制約を正しく表現します。

ワイドテーブルの作成

CREATE TABLE test_json_object_array(id VARCHAR, user JSON, primary key(id));

データの挿入

UPSERT INTO test_json_object_array(id, user) VALUES ('1001', '[{"name": "アリス", "age": 12}]');
UPSERT INTO test_json_object_array(id, user) VALUES ('1002', '[{"name": "アリス", "age": 9},{"name": "ボブ", "age": 20}]');
UPSERT INTO test_json_object_array(id, user) VALUES ('1003', '[{"name": "アリス", "age": 12},{"name": "ボブ", "age": 20}]');

検索インデックスの作成

2 つのアプローチが利用可能です。可能な限り、事前定義された構造を使用してください。

自動型推論

type=jsonarray を指定すると、システムは配列内の各オブジェクトについて、最初に書き込まれた値からフィールドタイプを推論します。後で書き込まれたデータが推論された型と一致しない場合はスキップされます。

CREATE INDEX idx USING SEARCH ON test_json_object_array(user(type=jsonarray));

フィールド構造の事前定義 (推奨)

自動型推論には、JSON オブジェクトの場合と同じ制限があります：トークン化クエリには明示的な text 型が必要であり、フィールド内のネストされたオブジェクトの配列には明示的な nested 型が必要です。

内部フィールド構造を定義するには mapping を使用します。トップレベルの型は nested である必要があります。フィールドタイプは Elasticsearch の構文に従います。

CREATE INDEX idx USING SEARCH ON test_json_object_array(
  user(mapping='{
    "type": "nested",
    "properties": {
      "name": {
        "type": "keyword"
      },
      "age": {
        "type": "integer"
      }
    }
  }')
);

フィールドのサブセットのみにインデックスを作成し、残りを無視するには、"dynamic": "false" を追加します：

CREATE INDEX idx USING SEARCH ON test_json_object_array(
  user(mapping='{
    "type": "nested",
    "dynamic": "false",
    "properties": {
      "name": {
        "type": "keyword"
      },
      "age": {
        "type": "integer"
      }
    }
  }')
);

クエリ

オブジェクトの配列をクエリするには、埋め込み Elasticsearch DSL を使用して SEARCH_QUERY 関数を使用します。path パラメーターを列名に設定した nested クエリを使用してください。

-- 単一の user オブジェクトで、名前が "アリス" であり、かつ年齢が 10 歳以上である行を検索
-- (各オブジェクト内のフィールドの関連付けを保持)
Lindorm> SELECT * FROM test_json_object_array WHERE SEARCH_QUERY('
  {
    "nested": {
      "path": "user",
      "query": {
        "bool": {
          "must": [
            { "match": { "user.name": "Alice" } },
            { "range": { "user.age": {"gte": 10} } }
          ]
        }
      }
    }
  }
');
+------+-----------------------------------------------------------+
| id   | user                                                      |
+------+-----------------------------------------------------------+
| 1003 | [{"name": "Alice", "age": 12},{"name": "Bob", "age": 20}] |
| 1001 | [{"name": "Alice", "age": 12}]                            |
+------+-----------------------------------------------------------+

id=1002 は、その "アリス" のエントリの age が 9 であり、10 未満であるため除外されます。ネストされたインデックスは同じオブジェクト内のフィールド間の関連付けを保持するため、アリスと age=9 は、ボブの年齢である 20 と混同されることなく、正しく一緒に評価されます。