分享下OceanBase-3.2.3.3中对JSON的支持（示例）

一起唠嗑
#1
功能 函数/操作符 示例
提取值 → / ->> info->>’$.name’(文本), info->’$.age’ (JSON)
更新/插入 JSON_SET JSON_SET(col, ‘$.k’, ‘v’)
仅插入 JSON_INSERT JSON_INSERT(col, ‘$.k’, ‘v’)
替换 JSON_REPLACE JSON_REPLACE(col, ‘$.k’, ‘v’)
删除 JSON_REMOVE JSON_REMOVE(col, ‘$.k’)
包含检查 JSON_CONTAINS JSON_CONTAINS(col, ‘val’, ‘$.array_key’)
数组长度 JSON_LENGTH JSON_LENGTH(col->’$.list’)
搜索路径 JSON_SEARCH JSON_SEARCH(col, ‘one’, ‘keyword’)
  • SQL示例
-- 建表
create table test.t_json_v3(id int primary key, info JSON);

-- 清理数据
truncate table test.t_json_v3;

-- 新增记录
insert into test.t_json_v3 values
(1, '{"name": "Fred", "age": 30, "city": "Shanghai", "skills": ["Java", "SQL"]}'),
(2, '{"name": "Wilma", "age": 28, "pets": ["dino"], "address": {"zip": "200000"}}'),
(3, '{"name": "Barney", "age": 32, "skills": ["C++", "Python", "Go"]}')
;

-- 查询示例
select id,info->'$.name' as name from test.t_json_v3;
select id,info->>'$.name' as name from test.t_json_v3;

-- 条件查询
select * from test.t_json_v3 where JSON_EXTRACT(info,'$.age') >= 29;

-- 格式化输出
select id, JSON_PRETTY(info) as formatted_info 
from test.t_json_v3 
where id = 1
;

-- 高级查询
-- -- 判断包含关系
select * from test.t_json_v3 
where JSON_CONTAINS(info->'$.skills', '"Java"')
;

-- -- 搜索任意层级
/* 查找 info 中任意位置包含 "Python" 的记录 (one 表示找到第一个即返回路径) */
select id, json_search(info, 'one', 'Python') as path 
from test.t_json_v3
;

/* 查找所有包含 "a" 的路径 (all 表示返回所有匹配路径的数组)*/
select id, json_search(info, 'all', '%a%') as paths 
from test.t_json_v3
;

-- -- 聚合函数（统计数组长度）
select 
    id, 
    info->>'$.name' as name,
    JSON_LENGTH(info->'$.skills') as skill_count 
from test.t_json_v3
;

-- -- 将多行数据聚合成一个 JSON 数组或对象
/* 将所有用户的名字聚合成一个 JSON 数组 */
select JSON_ARRAYAGG(info->>'$.name') as all_names 
from test.t_json_v3
;

/* 将 id 和 name 聚合成一个 json 对象 (id 为 key, name 为 value) */
select JSON_OBJECTAGG(id, info->>'$.name') as id_name_map 
from test.t_json_v3
;

-- 更新记录
/* ① JSON_SET: 路径存在则更新，不存在则插入（您已使用） */
update test.t_json_v3 
set info = JSON_SET(info,'$.city','Beijin') 
where id=1
;

/* ② JSON_INSERT: 仅当路径不存在时才插入，存在则忽略 */
update test.t_json_v3 
set info = JSON_INSERT(info, '$.hobby', 'reading') 
where id = 1
;

/* ③ JSON_REPLACE: 仅当路径存在时才更新，不存在则忽略 */
update test.t_json_v3 
set info = JSON_REPLACE(info, '$.age', 31) 
where id = 1
;

-- 删除记录
/* ① 删除字段 */
update test.t_json_v3 
set info = JSON_REMOVE(info, '$.city') 
where id = 1
;

/* ② 删除元素 */
update test.t_json_v3 
set info = JSON_REMOVE(info, '$.skills[0]') 
where id = 1
;
4 个赞
#2

今天的分享有点硬核哈，不过OB-V3.x中对JSON支持有限，还是V4版本中的JSON功能更强悍！

1 个赞
#3

的确，但是客户目前部署的是V3版本，只能基于现有环境使用了 :grinning: