AI 助理
备案控制台
官方文档
输入文档关键字查找
首页 云原生多模数据库 Lindorm 操作指南 宽表引擎（兼容HBase或Cassandra） 开发指南 搜索索引 使用手册 Lindorm JSON类型搜索索引使用手册

Lindorm JSON类型搜索索引使用手册

更新时间：
产品详情
我的收藏

本文介绍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}]                            |
+------+-----------------------------------------------------------+
上一篇：管理搜索索引下一篇：分区索引