Postgresql 处理jsonb字段
参考文档
https://www.cnblogs.com/zhangfx01/p/9506219.html
一、查询jsonb字段
select id,jsonb_v::jsonb->>'celldata' ,jsonb_v->>'celldata' from t_user where id=35;
update t_user set jsonb_v=jsonb_v||'{"address":"上海浦东"}'::jsonb where id=35; -- 添加address
update t_user set jsonb_v=jsonb_v||'{"address":"被冻结上海浦东"}'::jsonb where id=35; -- 修改address的值
update t_user set jsonb_v=jsonb_v - 'address' where id=35; -- 删除address
2、复杂对象
{ "myinfo": { "name": "xc", "myaddress": { "city": "上海", "pre": "区" } } } {"myinfo":{"name":"xc","myaddress":{"city":"上海","pre":"区"}}}
-- 添加到根节点下
update t_user set jsonb_v=jsonb_v||'{"myinfo":{"name":"xc","myaddress":{"city":"上海","pre":"区"}}}'::jsonb where id=35;
-- 增加一个内嵌age字段 {"myinfo":{"myaddress":{"age":12}}}
{ "myinfo": { "myaddress": { "age": 12 } } }
-- 错误! 以下方法把整个myinfo字段都作为修改 update t_user set jsonb_v=jsonb_v||'{"myinfo":{"myaddress":{"age":12}}}'::jsonb where id=35; -- 对myinfo->myaddress 增加一个age键 (其中true的作用为,没有就新增一个键) update t_user set jsonb_v= jsonb_set(jsonb_v,'{myinfo,myaddress,age}'::text[],'"124"',true) where id=35; update t_user set jsonb_v= jsonb_set(jsonb_v,'{myinfo,myaddress,age}'::text[],'12',true) where id=35;
3、List
-- 增加一个集合
update t_user set jsonb_v=jsonb_v||'{"celldata":[]}'::jsonb where id=35;
-- 数组中插入一条记录
update t_user set jsonb_v=jsonb_insert(jsonb_v::jsonb,'{celldata,0}','{"c":1,"r":1,"v":{"con":"str"}}',false) where id =35;
-- 删除集合
update t_user set jsonb_v=jsonb_v - 'celldata' where id=35;
-- 将celldata集合中第二个的v值整体改变
update t_user set jsonb_v= jsonb_set(jsonb_v,'{celldata,1,v}'::text[],'{"con":"new str"}',true) where id=35;
-- 将celldata集合中第二个v的某个key的值改变
update t_user set jsonb_v= jsonb_set(jsonb_v,'{celldata,1,v,con1}'::text[],'"new str"',true) where id=35;
-- 删除celldata集合中的第二个
update t_user set jsonb_v = jsonb_v #- '{celldata,1}' where id=35;
-- 集合在其他层级 添加
update t_user set jsonb_v=jsonb_v||'{"myinfo":{"celldata":[]}}'::jsonb where id=35;
-- 集合在其他层级 添加元素
update t_user set jsonb_v=jsonb_insert(jsonb_v,'{myinfo,celldata,0}','{"c":1,"r":1,"v":{"con":"str"}}',false) where id =35;
-- 集合在其他层级 添加元素到集合尾部
update t_user set jsonb_v=jsonb_insert(jsonb_v,'{myinfo,celldata,-1}','{"c":21,"r":21,"v":{"con":"str"}}',true) where id =35;
-- 集合在其他层级 元素修改
update t_user set jsonb_v= jsonb_set(jsonb_v,'{myinfo,celldata,0,v,con}'::text[],'"new str"',true) where id=35;
-- 查询时 返回修改过数据
select jsonb_agg( case when c1 @> '{"c":21,"r":21}' then '{"c":21,"r":21,"v":"vasfaf"}' else c1 end) from ( select jsonb_array_elements(jsonb_v->'myinfo'->'celldata') c1 from t_user where id=35 ) as t;
4、创建索引
Create index 索引名 on 表名(字段名) -- jsonb字段创建索引 create index idx_ay_json_v on ay_json_test using gin(json_value jsonb_path_ops );
5、创建自增ID
CREATE SEQUENCE gys.mytable_myid_seq CYCLE INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 99999999 CACHE 1; ALTER SEQUENCE gys.mytable_myid_seq OWNER TO postgres;
sql使用时
String sql = "insert into t_user (id,username,password,age,remark,updatetime,jsonb) values " + " (nextval('ay_json_test_id_seq'),?,?,?,?,now(),?)";
6、对jsonb字段中的key进行排序查询
select * from ay_json_test order by json_value::json->>'agent' desc order by jsonb字段名::json->>key desc/asc
7、对jsonb字段中的key进行筛选
select * from ay_json_test where json_value @>'{"agent":"02"}'::jsonb; Where jsonb字段名 @>’json串’::jsonb
8、jsonb字段返回的结果只获取其中几个key
-- 从普通字段以及jsonb字段中获取指定的几个key返回结果
select id,json_value::json->>'agent' as agent,json_value::json->>'ay_name' as ay_name from ay_json_test t ;
-- 从普通字段以及jsonb字段中获取指定的几个key返回json结果
select row_to_json(a.*) from (
select json_value::json->>'agent' as agent ,json_value::json->>'ay_name' as ay_name from ay_json_test t
) a;
9、按页返回数据 2个方法效率未知
-- 按ay_name 排序 获取第4条记录开始的记录
-- 方法一
SELECT a.* FROM ( SELECT t.*, ROW_NUMBER() over(ORDER BY json_value :: json ->> 'an_name' ) AS rn FROM ay_json_test t ) a WHERE rn > 3 and rn<6
-- 方法二
select * from ay_json_test order by json_value::json->>'ay_name' limit 2 offset 3
10、获取数组元素数量
select jsonb_array_length(jsonb_v->'myinfo'->'celldata') from t_user where id=35 ; -- 或者 select jsonb_array_length(c1) from ( select jsonb_v->'myinfo'->'celldata' as c1 from t_user where id=35 ) as t;
11、获取jsonb字段长度
select jsonb_v as v, CHAR_LENGTH(jsonb_v :: text) as l, LENGTH(jsonb_v :: text) as l2, CHARACTER_LENGTH(jsonb_v :: text) as l3, octet_length(jsonb_v :: text) as l4 from t_user;
12、复杂处理
C=1 r=2 修改v
Insert into t_user(id,jsonb_v) values(113,’[{"c":2,"r":2,"v":{"con":"str"}},{"c":1,"r":1,"v":{"con":"new str"}},{"c":21,"r":21,"v":{"con":"str"}},{"c":20,"r":21,"v":{"con":"str"}}]’);
先分拆数据,得到最终结果
select jsonb_agg(col2) from ( select row_to_json(t2) col2 from ( select c, r , case when c=20 and r=21 then '{"k":1,"y":"2"}' else v end v from (select jsonb_v from t_user where id=113) as t1, jsonb_to_recordset ( t1.jsonb_v ) x ( C INT, r INT, v jsonb ) ) t2 ) t3
c>=20 c=c+20
select jsonb_agg(col2) from ( select row_to_json(t2) col2 from ( select c, case when r>= 20 then r+20 else r end r, v from (select jsonb_v from t_user where id=113) as t1, jsonb_to_recordset ( t1.jsonb_v ) x ( C INT, r INT, v jsonb ) ) t2 ) t3
完