Lindorm:Lindorm の JSON データ型に対する検索インデックスのユーザーガイド

JSON_CONTAINS クエリ

-- c1 列には "101" と "999" の両方は含まれていません。
Lindorm> SELECT * FROM test_json_array WHERE JSON_CONTAINS(c1, '["101", "999"]');
Empty set (0.11 sec)

-- c1 列には "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 クエリ

-- json_contains_any は、いずれかの要素がヒットした場合に結果を返します。id=1001 の c1 列には "101" が、id=1002 の c1 列には "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 列の user_info は JSON オブジェクトです。検索インデックスを作成する際に type=jsonobject を指定できます。これにより、システムは最初に書き込まれた値に基づいてフィールドの型を自動的に推論します。例えば、user_info.name は文字列型、user_info.age は数値型として推論されます。後続のデータが推論された型と一致しない場合、システムはインデックスの一貫性を保つために無効なデータをスキップします。

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

サブオブジェクト構造の事前定義 (推奨)

自動的な型推論に依存すると、予期しない型になる可能性があります：

• 内部フィールドに対してトークン化されたクエリを実行するには、トークン化のためのフィールド型を明示的に指定する必要があります。

• 複雑な JSON オブジェクトにネストされたオブジェクトの配列が含まれている場合、フィールド型を nested として明示的に指定する必要があります。

また、mapping を使用して内部構造を指定し、必要なフィールド型を事前に定義することもできます。

-- サブフィールドの型を事前定義
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"
      }
    }
  }')
);

JSON オブジェクト内の一部のフィールドのみにインデックスを作成したい場合は、"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_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)

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)

JSON_EXTRACT と MATCH の組み合わせクエリ

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

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)

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)

内部オブジェクトの自動的な型推論

JSON 列の user はオブジェクトの配列です。検索インデックスを作成する際に type=jsonarray を指定できます。これにより、システムは配列内の各オブジェクトについて、最初に書き込まれた値に基づいてフィールドの型を自動的に推論します。例えば、user.name は文字列型、user.age は数値型として推論されます。後続のデータが推論された型と一致しない場合、システムはインデックスの一貫性を保つために無効なデータをスキップします。

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

サブオブジェクト構造の事前定義 (推奨)

自動的な型推論に依存すると、予期しない型になる可能性があります：

• 内部フィールドに対してトークン化されたクエリを実行するには、トークン化のためのフィールド型を明示的に指定する必要があります。

• 複雑な JSON オブジェクトにネストされたオブジェクトの配列が含まれている場合、フィールド型を nested として明示的に指定する必要があります。

mapping を使用して内部構造を指定し、必要なフィールド型を事前に定義することができます。

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

JSON オブジェクト内の一部のフィールドのみにインデックスを作成したい場合は、"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"
      }
    }
  }')
);

クエリメソッド

SEARCH_QUERY 関数を使用してのみ、オブジェクトの配列をクエリできます。次の例に示すように、SQL クエリ内に Elasticsearch DSL 構文を埋め込む必要があります。

-- user.name が Alice で、かつ user.age が 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}]                            |
+------+-----------------------------------------------------------+