PostgreSQL的jsonb类型中包含数组的 增加、遍历、查找、修改
参考：postgresql中文手册 http://www.postgres.cn/docs/11/index.html
表结构
CREATE TABLE person(id serial, info jsonb);
JSONB结构
[{"num":"学号","name":"姓名","score":"成绩"}, {"num":"学号","name":"姓名","score":"成绩"}]
插入
INSERT INTO person (info) VALUES ('[{"num":"1","name":"张三","score":"90"}]'::jsonb);
增加
UPDATE person SET info = info || '[{"num":"2","name":"李四","score":"91"}]'::jsonb;
遍历
SELECT t.* FROM person, jsonb_to_record(info) AS t(num text, name text, score text) WHERE person.id=1;
或
CREATE TABLE rt_person_info(no text, name text, score text);
SELECT t.* FROM person, jsonb_populate_recordset(null::rt_person_info, info) WHERE person.id=1;
查找
SELECT * FROM person WHERE info @> '[{"num":"1"}]'::jsonb;
修改
UPDATE person t1 SET info = jsonb_set(info, array[(SELECT ORDINALITY::INT - 1 FROM person t2, jsonb_array_elements(info) WITH
ORDINALITY WHERE t1.id = t2.id AND value->>'num' = '1')::text, 'score'::text], '"92"') WHERE id = '1'