postgreSQL的json字段

创建含有json数据字段的表

create table test_json1(id serial primary key , name json);--创建含有json数据字段的表

插入数据

insert into test_json1(name) values('{"col1":1,"col2":"francs","col3":"male"}');
insert into test_json1(name) values('{"col1":1,"col2":"huang","col3":"东"}');
insert into test_json1(name) values('{"name":"hds","password":"123456","email":"123@qq.com"}');

查询json数据

select name -> 'name' from test_json1; --查询json数据的键值
select name ->>'col2' as json_text from test_json1;--以文本形式返回

追加、删除、更新

select name::jsonb || '{"phone":"192168"}'::jsonb from test_json1 where id = 4;--显示追加
update test_json1 set name = name::jsonb || '{"phone":"192168"}'::jsonb where id = 4; --追加，实际追加
	
select name::jsonb - 'phone' from test_json1 where id = 4;--显示删除，实际没有删除
update test_json1 set name = name::jsonb -'phone' where id = 4; --删除，实际删除
	
update test_json1 set name = name::jsonb || '{"phone":"110"}'::jsonb where id = 4;--更新数据