postgresql中使用json

jsonb与json的差异

jsonb:存储格式为二进制，二进制文件是已解析好的数据，检索jsonb时不需要重新解析

json:存储格式为文本，检索json时必须重新解析

对json数据的操作：

postgres=# create table test_json1 (id serial primary key,name json);
CREATE TABLE

postgres=# insert into test_json1(name) values ('{"col1":2,"col2":"ftp","col3":"female"}');
INSERT 0 1

postgres=# select * from test_json1;
id | name
----+-----------------------------------------
1 | {"col1":2,"col2":"ftp","col3":"female"}
2 | {"col1":2,"col2":"ftp","col3":"male"}
(2 rows)

postgres=# select name -> 'col2' from test_json1 where id=1;
?column?
----------
"ftp"
(1 row)

postgres=# select name -> 'col3' from test_json1 where id=1;
?column?
----------
"female"
(1 row)

postgres=# select name ->> 'col3' from test_json1 where id=1;（>>表示以文本格式返回）
?column?
----------
female
(1 row)