例一:
create table t_stu2(
sid text primary key,
sname text,
score jsonb
)
insert into t_stu2 values('01','lili','[{"学科":"math","成绩":"90"},{"学科":"english","成绩":"85"}]');
insert into t_stu2 values('02','keke','[{"学科":"math","成绩":"89"},{"学科":"english","成绩":"88"}]');
insert into t_stu2 values('03','heh','[{"学科":"math","成绩":"98"},{"学科":"english","成绩":"55"}]');
insert into t_stu2 values('04','li','[{"学科":"math","成绩":"78"},{"学科":"english","成绩":"78"}]');
insert into t_stu2 values('05','kety','[{"学科":"math","成绩":"77"},{"学科":"english","成绩":"57"}]');
insert into t_stu2 values('06','lucy','[{"学科":"math","成绩":"88"},{"学科":"english","成绩":"88"}]');
insert into t_stu2 values('07','sandy','[{"学科":"math","成绩":"44"},{"学科":"english","成绩":"68"}]');
update t_stu2 set score = score || '{"学科":"语文","成绩":"99"}' where sid='02';/*增*/
update t_stu2 set score = jsonb_set(score,'{0,成绩}','"100"',false) where sid='03';/*改*/
update t_stu1 set score = '{"语文":"99"}' || score where sid = '07';
/*update t_stu2 set score=jsonb_insert(score, '{-1}', '{"学科":"云计算","成绩":"90"}', true) where sid='03';*/
update t_stu1 set score = jsonb_set(score,'{english}','"32"',false) where sid='03';/*改*/
update t_stu1 set score = score-'english' where sid ='02';/*删*/
update t_stu1 set score = score #- '{0,stock}' where sid ='07'
例二:
create table t_stu(
sid text primary key,
sname text,
cname jsonb,
grade jsonb
)
create table t_stu1(
sid text primary key,
sname text,
score jsonb
)
select grade from t_stu where sid like '%0%'
insert into t_stu values('01','lili','{"001":"math","002":"english"}','{"001":"90","002":"100"}');
insert into t_stu values('02','xuxu','{"001":"math","002":"english"}','{"001":"78","002":"85"}');
insert into t_stu values('03','kei','{"001":"math","002":"english"}','{"001":"85","002":"85"}');
insert into t_stu values('04','sky','{"001":"math","002":"english"}','{"001":"62","002":"75"}');
insert into t_stu values('05','meimei','{"001":"math","002":"english"}','{"001":"98","002":"88"}');
insert into t_stu values('06','lucy','{"001":"math","002":"english"}','{"001":"90","002":"92"}');
insert into t_stu1 values('02','hah','{"math":"93","english":"81"}');
insert into t_stu1 values('03','lucy','{"math":"92","english":"88"}');
insert into t_stu1 values('04','John','{"math":"93","english":"77"}');
insert into t_stu1 values('05','jaccicar','{"math":"96","english":"97"}');
insert into t_stu1 values('06','hanmiemei','{"math":"60","english":"79"}');
insert into t_stu1 values('07','mam','{"math":"97","english":"78"}');
insert into t_stu1 values('08','enmm','{"math":"70","english":"76"}');
insert into t_stu1 values('09','suibianla','{"math":"80","english":"67"}');
select info->'score' from t_stu2 where info @> '{"sid":"02"}';
例三:
create table t_stu1(
id serial primary key,
info jsonb
)
insert into t_stu1(info) values('{"sid":"06","sname":"l6","math":"84","english":"98"}');
insert into t_stu1(info) values('{"sid":"02","sname":"l2","math":"83","english":"98"}');
insert into t_stu1(info) values('{"sid":"03","sname":"l3","math":"97","english":"77"}');
insert into t_stu1(info) values('{"sid":"04","sname":"l4","math":"67","english":"67"}');
insert into t_stu1(info) values('{"sid":"05","sname":"l5","math":"78","english":"66"}');
select * from t_stu1 where info @> '{"sid":"06"}'
update t_stu1 set info=jsonb_modify(info,'$.sname','mike') where info @> '{"sid":"06"}'