瑞蓝创软件

2026-07-09

导读：兼容 MySQL/Oracle 双模式，可直接复制的 SQL 脚本

作者简介：任思聪，瑞蓝创数据库工程师

原创内容未经授权不得随意使用，转载请联系小编并注明来源

JSON是什么

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，具有易读易写的特点，广泛应用于Web开发和数据传输领域。

JSON数据由键值对组成，每个键值对之间用逗号分隔，整个数据以大括号 {} 包裹表示一个对象，或者以中括号 [] 包裹表示一个数组。基本语法结构如下：

● 对象（Object）：使用大括号 {} 包裹，键值对之间使用冒号 : 分隔，如{ "name": "John", "age": 30 }。

● 数组（Array）：使用中括号 [] 包裹，元素之间使用逗号 , 分隔，如 [ "apple", "banana", "orange" ]。

{
  "code":"200",
  "data":{
    "RequestId":"C4280312-AA02-50EB-9A68-F71CFC6DE58E",
    "IsInWhitelist":true
  },
  "httpStatusCode":"200",
  "requestId":"C4280312-AA02-50EB-9A68-F71CFC6DE58E",
  "successResponse":true
}

● oracle数据库：对于Oracle 21C，json对象或数组的最大嵌套深度为1000。json类型的列名称最大长度为255bytes

● Mysql数据库：对于mysql 5.7 版本，json对象或数组的最大嵌套深度为99

● OceanBase 数据库：最大嵌套层数为100，V4.2.1 BP7 版本引入参数json_document_max_depth用于设置 JSON 文档中允许的最大嵌套层数，[100, 1024]，

可参考：http://oceanbase.com/docs/common-oceanbase-database-cn-1000000000833393

OB与JSON

OceanBase V3.2 版本在 MySQL 模式下支持了 JSON 数据类型，V4.1 版本支持 Oracle 模式下的 JSON 数据类型，在V4.2.1之后的版本中不断进行优化和增强

2.1 depth和length

深度和长度

深度计算：

空数组、空对象或标量值的深度为 1。

仅包含深度为 1 的元素的非空数组深度为 2，仅包含深度为 1 的成员值的非空对象的深度为 2。否则，JSON 文档的深度大于 2。

长度计算：

标量的长度为 1，空数组、空对象长度为0。

数组的长度是数组元素的数量。

对象的长度是对象成员的数量。

不计算嵌套数组或对象的长度。

SET @jn = '
{
  "layer1": {
    "layer2": {
      "layer3": {
        "layer4": {
          "layer5": {
            "info": "hahaha"
          }
        }
      }
    }
  }
}'

SELECT JSON_DEPTH(@jn);   -- 7
SELECT JSON_LENGTH(@jn);  -- 1

2.2 查询

查询示例：

CREATE TABLE json_t1 (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  info JSON
);

INSERT INTO json_t1 (info) VALUES 
('{"name": "Alice", "age": 28, "email": "alice@123.com"}'),
('{"name": "Bob", "age": 42, "skills": ["Java", "Python", "SQL"]}'),
('{
  "code":"200",
  "data":{
    "RequestId":"C4280312-AA02-50EB-9A68-F71CFC6DE58E",
    "IsInWhitelist":true
  },
  "httpStatusCode":"200",
  "requestId":"C4280312-AA02-50EB-9A68-F71CFC6DE58E",
  "successResponse":true
}'),
{
"layer1": {
    "layer2": {
      "layer3": {
        "layer4": {
          "layer5": {
            "info": "hahaha"
          }
        }
      }
    }
  }
}');

将结果以JSON形式输出：

/* 提取某一字段
-&gt; 操作符返回的是JSON类型（保留引号）
-&gt;&gt; 操作符返回的是字符串类型（去掉引号）*/
SELECT info-&gt;'$.name' FROM json_t1;
SELECT info-&gt;&gt;'$.name' FROM json_t1;

-- 提取嵌套字段
SELECT info-&gt;'$.skills[0]' FROM json_t1 WHERE id = 2;
SELECT info-&gt;'$.data.RequestId' FROM json_t1 WHERE id = 3;
SELECT info-&gt;'$.layer1.layer2.layer3.layer4.layer5.info' FROM json_t1 WHERE id = 4;

-- 查询记录
SELECT * FROM json_t1 WHERE info-&gt;'$.data.RequestId' IS NOT NULL;

-- JSON_CONTAINS(target, candidate[, path])
SELECT * FROM json_t1 WHERE JSON_CONTAINS(info, '"Java"', '$.skills');

-- 路径表达式 $ 表示JSON文档的根
SELECT * FROM json_t1 WHERE JSON_LENGTH(info-&gt;'$') &gt; 3;
create table t1(id int primary key, name varchar(20));
insert into t1 values (1,'ob'),(2,'mysql');

-- 将所有列的数据转换成 JSON 数据，并以数组形式输出
SELECT json_arrayagg(name) FROM t1; 
-- 转换成一个 JSON 格式的对象，包含了前面输入的所有 key-value 对
SELECT JSON_OBJECT("name",name) FROM t1;

SELECT JSON_ARRAYAGG(JSON_OBJECT("id", id, "name", name)) AS t1 FROM t1;

执行计划以JSON形式输出：

EXPLAIN FORMAT = JSON
SELECT JSON_ARRAYAGG(JSON_OBJECT("id", id, "name", name)) AS t1 FROM t1;

OceanBase 数据库 MySQL 模式下所支持的 JSON 函数：https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002017579

2.3 对JSON字段创建索引

CREATE TABLE api (
    id INT AUTO_INCREMENT PRIMARY KEY,
    response_data JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE api ADD KEY idx1(response_data);
--- ErrorCode = 3152, SQLState = 42000, Details = JSON column 'response_data' cannot be used in key specification.

2.3.1 生成列+ 普通索引

-- JSON_EXTRACT：从 JSON 文档中指定的路径返回数据
-- JSON_UNQUOTE：取消引用 JSON 值并将结果作为 utf8mb4 字符串返回
ALTER TABLE api ADD res_code varchar(100) GENERATED ALWAYS AS (json_unquote(json_extract (`response_data`, '$.code'))) virtual;  -- online
ALTER TABLE api ADD KEY idx_code(res_code);

2.3.2 函数索引

CREATE INDEX idx_reqid_fx ON api (
  (CAST(`response_data` -&gt;&gt; '$.data.RequestId' AS CHAR(255)))
);

2.3.3 多值索引

https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002015436

https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002910917

多值需要在索引定义中使用CAST(... AS ... ARRAY)，将JSON数组中同类型的标量值转换为SQL数据类型数组。这一操作将创建一个虚拟列，该列自动填充 SQL 数组类型的值；紧接着，该虚拟列上会创建一个函数索引。多值索引默认只支持预建索引，不支持后建索引。

优化器在where子句中指定了以下函数时，会使用多值索引来查询

● MEMBER OF()

● JSON_CONTAINS()

● JSON_OVERLAPS

CREATE TABLE user_info (
    user_id BIGINT,
    name VARCHAR(1024),
    age BIGINT,
    hobbies JSON,
    INDEX idx1 ((CAST(hobbies-&gt;'$[*]' AS CHAR(512) ARRAY)))
);
insert into user_info values(1, "LiLei", 18, '["reading", "knitting", "hiking"]'),
    (2, "HanMeimei", 17, '["reading", "Painting", "Swimming"]'),
    (3, "XiaoMing", 19, '["hiking", "Camping", "Swimming"]');


ALTER TABLE user_info ADD hobbies2 varchar(100) GENERATED ALWAYS AS (json_unquote(json_extract (`hobbies`, '$[*]'))) virtual;
ALTER TABLE user_info ADD KEY idx2(hobbies2);

CREATE INDEX idx3 ON user_info (
    (CAST(`hobbies` -&gt;&gt; '$[*]' AS CHAR(255)))
);

-- 查找所有hobbies中包含Swimming的记录
select user_id, name from user_info where JSON_CONTAINS(hobbies-&gt;'$[*]', CAST('["Swimming"]' AS JSON));
select /*+ index(idx2) */ user_id, name from user_info where JSON_CONTAINS(hobbies-&gt;'$[*]', CAST('["Swimming"]' AS JSON));

SELECT user_id, name FROM user_info WHERE CAST(hobbies -&gt;&gt; '$[*]' AS CHAR(255)) = CAST('["Swimming"]' AS CHAR(255));
SELECT user_id, name FROM user_info WHERE CAST(hobbies -&gt;&gt; '$[*]' AS CHAR(255)) = '["reading", "Painting", "Swimming"]';

多值索引可以定义为唯⼀键。如果将其定义为唯⼀键，尝试插⼊已存在于多值索引中的值会返回重复键错误。如果已经存在重复值，尝试添加唯⼀的多值索引会失败

CREATE TABLE user_info2 (
    user_id BIGINT,
    name VARCHAR(1024),
    age BIGINT,
    hobbies JSON,
    UNIQUE INDEX idx1 ((CAST(hobbies-&gt;'$[*]' AS CHAR(512) ARRAY)))
);

insert into user_info2 values(1, "LiLei", 18, '["reading", "knitting", "hiking"]');

-- ErrorCode = 1062, SQLState = 23000, Details = Duplicate entry 'reading' for key 'idx1'

JSON 多值索引会占用额外存储空间，并可能对写入性能造成影响，当对包含多值索引的 JSON 字段进行修改（如插入、更新、删除操作）时，索引也会随之更新，写入的开销会变大。因此在使用过程中需要权衡JSON 多值索引的优缺点，按需创建。

使用限制

OceanBase 数据库 V4.3.1 及之后版本（MySQL 模式，Oracle 模式目前还不支持 JSON多值索引）
CAST(response_data->'$.data' AS ...在建表时不会报错，但是插入数据时会报错，不支持将一个 JSON 对象转换为 CHAR(255) 数组
默认只支持预建索引，不支持后建索引。

a. _enable_add_fulltext_index_to_existing_table 用于管理是否开启后建全文索引，包含 ALTER TABLE 以及 CREATE FULLTEXT INDEX 两种后建 SQL 语句， sys 租户设置。

b. https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002015726

多值索引不支持用作主键

2.3.4 对比

2.4 Json Partial Update能力

需要开启log_row_value_options参数，参考官方文档：https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000002015916，实现对JSON字段的部分更新。

当前 OceanBase 数据库 MySQL 模式可以进行 Partial Update 的 Json 表达式如下：

● json_set 或 json_replace：用于更新 Json 字段的值。

● json_remove：用于删除 Json 字段。

select * from json_t1;

UPDATE json_t1 
SET info = json_replace(
  info,
'$.age', 30,
'$.skills', JSON_ARRAY('Java', 'SQL')
) 
WHERE id = 2;

UPDATE json_t1 
SET info = json_set(info, '$.skills[0]', 'C++') 
WHERE id = 2;

UPDATE json_t1 SET info = json_remove(info, '$.name') WHERE id = 1;

2.4.1 Chunk Size 更新粒度

OceanBase 数据库的 Json 的数据基于 Lob 存储的，而 Lob 底层是分块存储的，所以每次 Partial Update 的数据量最小为一个 Lob 分块。如果 Lob 分块越小，那么写的数据量就会越小。为此也提供了设置 Lob 分块大小的 DDL 语法，可以在创建列时指定。

CREATE TABLE json_test(pk INT PRIMARY KEY, j JSON CHUNK '4k');

Chunk Size 不能无限小，太小了会影响 SELECT、INSERT 和 DELETE 的性能。一般建议根据 Json 文档的平均字段大小来设置，如果大部分字段都很小，那可以设置为 1K。OceanBase 数据库为了优化 Lob 类型的读，对于小于 4K 的数据，就直接 INROW 存储了，此时是不会进行 Partial Update。Partial Update 主要还是为了提高大文档更新的性能，小文档全量更新性能反而更好。

如果在操作过程中遇到问题，欢迎在评论区留言交流，后续我们会持续分享更多实战运维干货，记得关注不迷路，下次见~

END

瑞蓝创 OceanBase OBCP V4 精英训练营