Lindorm JSON類型搜尋索引使用手冊 - Lindorm

本文介紹JSON資料類型如何建立搜尋索引。

前提條件

Lindorm寬表引擎為2.8.5及以上版本。如何查看或升級目前的版本，請參見升級小版本。
LindormLTS引擎為3.8.13.3及以上版本。如何查看或升級目前的版本，請參見升級小版本。

使用說明

在建立搜尋索引時，需要根據JSON實際使用的不同模式，指定不同的索引欄位類型，主要分為以下三種模式：

基礎元素數組
頂層元素為數組，且資料中每個元素都是基礎元素。例如，數值數組[1, 2, 3]，字串數組["1", "2", "3"]。
JSON對象
頂層元素為JSON對象。例如{"id": "GTY102289", "scores": [1.1, 2.9], "extra": {"A": "daew", "B": 88.99}}。
對象數組
頂層元素為數組，且資料中每個元素是JSON對象。例如[{"name": "Alice", "address": "XX Street"},{"name": "Bob"}]。

重要

以上三種模式不能混用，不合法資料可能會被跳過，導致無法索引。

使用方式

下文將詳細介紹基礎元素數組、JSON對象、對象數組這三種模式對應的建立索引的方式、以及支援的SQL查詢方式，並提供相應的樣本。

基礎元素數組

建立寬表

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]');

建立搜尋索引

JSON列c1是字串數組，建立搜尋索引時需指定mapping='{"type": "keyword"}'

JSON列c2是INT數組，建立搜尋索引時需指定mapping='{"type": "integer"}'

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

查詢方式

對基礎元素數組而言，目前SQL層支援以下兩種查詢方式：

JSON_CONTAINS：要求所有候選集合約時滿足條件。
JSON_CONTAINS_ANY：候選集合中任一元素滿足條件即可返回。

`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對象

建立寬表

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

寫入資料

UPSERT INTO test_json_object(id, user_info) VALUES ('1001', '{"name": "Alice", "age": 13, "address": "浙江省杭州市", "hobbies": ["play games", "read", "badminton"]}');
UPSERT INTO test_json_object(id, user_info) VALUES ('1002', '{"name": "Bob", "age": 9, "address": "浙江省寧波市", "hobbies": ["play games"]}');
UPSERT INTO test_json_object(id, user_info) VALUES ('1003', '{"name": "John", "age": 21, "address": "廣東省深圳市", "hobbies": ["read", "badminton", "food"]}');

建立搜尋索引

分為內部物件類型依賴自動推斷和預定義子物件結構兩種方式。

內部物件類型依賴自動推斷

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方式指定內部結構，預定義所需要的類型。

說明

通過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"
      }
    }
  }')
);

若只希望對JSON中的部分欄位建索引，可增加"dynamic": "false"參數，忽略JSON的其他欄位。

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對象支援以下查詢方式：

支援JSON_EXTRACT系列函數查詢。
支援對JSON內部基礎元素數組元素使用JSON_CONTAINS、JSON_CONTAINS_ANY查詢。
支援JSON_EXTRACT和MATCH組合查詢。

但請注意，目前不支援JSON_EXTRACT、JSON_CONTAINS和完整對象的匹配查詢。例如where JSON_EXTRACT(json_col, '$.user') = '{"name": "Alice", "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": "浙江省杭州市", "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": "廣東省深圳市", "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 ('浙江');
+------+---------------------------------------------------------------+
| id   | user_info                                                     |
+------+---------------------------------------------------------------+
| 1001 | {"name": "Alice", "age": 13, "address": "浙江省杭州市", "hobbies": ["play games", "read", "badminton"]}                                          |
| 1002 | {"name": "Bob", "age": 9, "address": "浙江省寧波市", "hobbies": ["play games"]}                                                               |
+------+---------------------------------------------------------------+
2 rows in set (0.03 sec)

Lindorm> SELECT * FROM test_json_object WHERE MATCH(JSON_EXTRACT(user_info, '$.address')) AGAINST ('杭州');
+------+---------------------------------------------------------------+
| id   | user_info                                                     |
+------+---------------------------------------------------------------+
| 1001 | {"name": "Alice", "age": 13, "address": "浙江省杭州市", "hobbies": ["play games", "read", "badminton"]}                                   |
+------+---------------------------------------------------------------+
1 row in set (0.01 sec)

指定path執行`JSON_CONTAINS`查詢

Lindorm> SELECT * FROM test_json_object WHERE JSON_CONTAINS(user_info, '["read"]', '$.hobbies');
+------+---------------------------------------------------------------------------------------------------------------+
| id   | user_info                                                                                                     |
+------+---------------------------------------------------------------------------------------------------------------+
| 1003 | {"name": "John", "age": 21, "address": "廣東省深圳市", "hobbies": ["read", "badminton", "food"]}              |
| 1001 | {"name": "Alice", "age": 13, "address": "浙江省杭州市", "hobbies": ["play games", "read", "badminton"]}       |
+------+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.04 sec)

對象數組

建立寬表

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

寫入資料

UPSERT INTO test_json_object_array(id, user) VALUES ('1001', '[{"name": "Alice", "age": 12}]');
UPSERT INTO test_json_object_array(id, user) VALUES ('1002', '[{"name": "Alice", "age": 9},{"name": "Bob", "age": 20}]');
UPSERT INTO test_json_object_array(id, user) VALUES ('1003', '[{"name": "Alice", "age": 12},{"name": "Bob", "age": 20}]');

建立搜尋索引

分為內部物件類型依賴自動推斷和預定義子物件結構兩種方式。

內部物件類型依賴自動推斷

JSON列user是一個對象數組，建立搜尋索引時需要指定type=jsonarray。系統會根據實際數組中每個對象首次寫入的值自動推斷欄位類型：例如user.name會推斷為字串類型，user.age會推斷為數實值型別。若後續寫入資料不符合首次推斷的類型，系統將跳過非法資料，確保索引一致性。

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

預定義子物件結構（推薦）

依賴自動推斷可能會導致推斷的類型不符合預期：

對內部欄位進行分詞查詢，需要顯式指定欄位類型為分詞。
複雜JSON內部嵌套了對象數組，必須顯式指定對應欄位類型為nested。

您可通過mapping方式指定內部結構，預定義所需要的類型。

說明

通過mapping方式指定內部結構時，內部欄位類型相容Elasticsearch文法。

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

若只希望對JSON中的部分欄位建索引，可增加"dynamic": "false"參數，忽略JSON的其他欄位。

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}]                            |
+------+-----------------------------------------------------------+

前提條件

使用說明

使用方式

基礎元素數組

建立寬表

寫入資料

建立搜尋索引

查詢方式

JSON_CONTAINS查詢

JSON_CONTAINS_ANY查詢

JSON對象

建立寬表

寫入資料

建立搜尋索引

內部物件類型依賴自動推斷

預定義子物件結構（推薦）

查詢方式

JSON_EXTRACT查詢

JSON_EXTRACT+MATCH組合

指定path執行JSON_CONTAINS查詢

對象數組

建立寬表

寫入資料

建立搜尋索引

內部物件類型依賴自動推斷

預定義子物件結構（推薦）

查詢方式

`JSON_CONTAINS`查詢

`JSON_CONTAINS_ANY`查詢

`JSON_EXTRACT`查詢

`JSON_EXTRACT`+`MATCH`組合

指定path執行`JSON_CONTAINS`查詢