创建json 类型字段表

teledb=# create table t_json(id int,f_json json);
CREATE TABLE

插入数据

teledb=# insert into t_json values(1,'{"col1":1,"col2":"teledb"}');
INSERT 0 1
teledb=# insert into t_json values(2,'{"col1":1,"col2":"teledb","col3":"pgxz"}');
INSERT 0 1
teledb=# select * from t_json;
 id |                  f_json                   
----+-------------------------------------------
  1 | {"col1":1,"col2":"teledb"}
  2 | {"col1":1,"col2":"teledb","col3":"pgxz"}
(2 rows)

通过键获得json对象域

teledb=# select f_json ->'col2' as col2 ,f_json -> 'col3' as col3 from t_json; 
   col2    |  col3  
-----------+--------
 "teledb" | 
 "teledb" | "pgxz"
(2 rows)

以文本形式获取对象值

teledb=# select f_json ->>'col2' as col2 ,f_json ->> 'col3' as col3 from t_json;
  col2   | col3 
---------+------
 teledb | 
 teledb | pgxz
(2 rows)

teledb=# select f_json ->>'col2' as col2 ,f_json ->> 'col3' as col3 from t_json where f_json ->> 'col3' is not null;
  col2   | col3 
---------+------
 teledb | pgxz
(1 row)