json应用
更新时间 2025-02-14 10:25:03
最近更新时间: 2025-02-14 10:25:03
本文为您介绍json应用的相关操作。
创建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)
