腾讯云数据仓库 TCHouse-P json/jsonb 的使用

二、创建 json 类型字段表

“`sqlcreate table t_json(id int,f_json json);

三、插入数据

insert into t_json values(1,'{"col1":1,"col2":"tbase"}');insert into t_json values(2,'{"col1":1,"col2":"tbase","col3":"pgxz"}');

四、通过键获得 JSON 对象域

select f_json ->'col2' as col2 ,f_json -> 'col3' as col3 from t_json;

五、以文本形式获取对象值

select f_json ->>'col2' as col2 ,f_json ->> 'col3' as col3 from t_json;

八、创建 jsonb 类型字段表

create table t_jsonb(id int,f_jsonb jsonb);

九、插入数据

insert into t_jsonb values(1,'{"col1":1,"col2":"tbase"}');insert into t_jsonb values(2,'{"col1":1,"col2":"tbase","col3":"pgxz"}');

·jsonb 插入时会移除重复的键。

十、更新数据

·增加元素

update t_jsonb set f_jsonb = f_jsonb || '{"col3":"pgxz"}'::jsonb where id=1;

·更新原来的元素

update t_jsonb set f_jsonb = f_jsonb || '{"col2":"tbase"}'::jsonb where id=3;

·删除某个键

update t_jsonb set f_jsonb = f_jsonb – 'col3';

十一、jsonb_set()函数更新数据

update t_jsonb set f_jsonb = jsonb_set( f_jsonb , '{col}', '"pgxz"' , true ) where id=1;update t_jsonb set f_jsonb = jsonb_set( f_jsonb , '{col}', '"pgxz"' , false ) where id=2;update t_jsonb set f_jsonb = jsonb_set( f_jsonb , '{col2}', '"pgxz"' , false ) where id=3;

十三、jsonb_each() 将 json 对象转变键和值

select * from jsonb_each((select f_jsonb from t_jsonb where id=1));

十四、jsonb_each_text() 将 json 对象转变文本类型的键和值

select * from jsonb_each_text((select f_jsonb from t_jsonb where id=1));

十五、row_to_json() 将一行记录变成一个 json 对象

select row_to_json(tbase) from tbase;

{"id":1,"nickname":"tbase"}{"id":2,"nickname":"pgxz"}

十六、json_object_keys()返回一个对象中所有的键

select * from json_object_keys((select f_jsonb from t_jsonb where id=1)::json);

十八、jsonb 索引使用

TBase 为文档 jsonb 提供了 GIN 索引，GIN 索引可以被用来有效地搜索在大量 jsonb 文档（数据）中出现的键或者键值对。

十九、创建立 jsonb 索引

create index t_jsonb_f_jsonb_idx on t_jsonb using gin(f_jsonb);

20、测试查询的性能

·没有索引开销

select * from t_jsonb where f_jsonb @> '{"col1":9999}';

·有索引开销

select * from t_jsonb where f_jsonb @> '{"col1":9999}';