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"參數，忽略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_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)

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

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"參數，忽略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}]                            |
+------+-----------------------------------------------------------+