【例子】关于NoSql数据库的json数组的增、删、改、查

例一：

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"}'