PostgreSQL 数据库的 jsonb 数组操作

表结构

1
2
3
4
5
6
7
8
9
CREATE TABLE public.t_student_score_info
(
stu_id character varying(10) NOT NULL, -- 学号
score_info jsonb, -- 各科成绩
CONSTRAINT t_student_score_info_pkey PRIMARY KEY (stu_id)
);
COMMENT ON TABLE public.t_student_score_info IS '学生成绩表';
COMMENT ON COLUMN public.t_student_score_info.stu_id IS '学号';
COMMENT ON COLUMN public.t_student_score_info.score_info IS '各科成绩';

插入

1
insert into t_student_score_info values ('001', '[{"course":" 语文 ","score":99}]'::jsonb);

1600656558225

添加

1
update t_student_score_info set score_info = score_info || '[{"course":" 数学 ","score":88}]'::jsonb where stu_id = '001';

1600658414872

遍历

1
select t.stu_id, sf.* from t_student_score_info t, jsonb_to_recordset(score_info) as sf(course text, score integer);

1600659342980

查找

1,查找有“数学”科目的学生:

1
select * from t_student_score_info where score_info @> '[{"course":" 数学 "}]'::jsonb;

1600659393866

2,查找“语文”科目分数大于等于 100 分的同学:

1
2
select t.stu_id, sf.* from t_student_score_info t, jsonb_to_recordset(score_info) as sf(course text, score integer)
where course = '语文' and score >= 100;

1600659548969

修改

修改学号 001 同学的语文成绩为 70 分:

1
2
3
4
5
6
7
8
9
10
update t_student_score_info t1 set score_info = jsonb_set(
score_info,
array[
(select ordinality::int - 1 from t_student_score_info t2,
jsonb_array_elements(score_info) with ordinality
where t1.stu_id = t2.stu_id and value->>'course' = '语文')::text,
'score'
],
'70'
) where t1.stu_id = '001';

1600660063254