Postgresql的jsonb操作--存储对象/对象数组

create table demo(
    id   serial  NOT NULL PRIMARY KEY,
    name VARCHAR(20),
    info JSONB
);

insert into demo (name, info)values ('张三1', '{"age":12, "class":"二年级"}');
insert into demo (name, info)values ('张三', '{"age":16, "class":"五年级"}');
insert into demo (name, info)values ('张三3', '{"age":20, "class":"初一"}');

select * from demo where info @> '{"age":12}'::jsonb

select * from demo where (info->>'age')::int4 > 16

update demo as d set 
info = jsonb_set
( 
    (select info from demo where id = d.id)::jsonb,-- target 这是目的json数据，这里使用内部关联将对应的json查询出来
    '{class}', -- path 要修改的jsonb中对应的key
    '"语文"'::jsonb, -- new_value 替换的value
    false -- create_missing:: true:- 如果不存在对应的key值，则新增，反之，false - 不做其他操作，这里可有可无
) where id = 1;

insert into demo (name, info)values ('李四', '[{"age":8, "class":"一年级"},{"age": 10, "class": "三年级"}]');
insert into demo (name, info)values ('李四2', '[{"age":12, "class":"五年级"},{"age":16, "class":"初三"}]');
insert into demo (name, info)values ('李四3', '[{"age":19, "class":"高一"},{"age":8, "class":"一年级"}, {"age":10, "class":"三年级"}]');

update demo set info = info || '[{"age":9,"class":"二年级"}]'::jsonb where id = 1;

update demo d set info = jsonb_set(
    info,
    array[
        (select ORDINALITY::INT - 1 FROM demo d2,
         jsonb_array_elements(info) WITH ORDINALITY WHERE d.id = d2.id AND value->>'class' = '三年级')::text, -- 确定到准确的对象中
        'class' -- 需要修改的key
    ],
    '"四年级"' -- 替换的value
)where id = 1;

select * from demo where info @> '[{"class": "三年级"}]'; 

SELECT t.* FROM demo, jsonb_to_recordset(info) AS t(age int, class text) WHERE demo.id=7;