json應用
更新時間 2025-02-14 10:22:26
最近更新時間: 2025-02-14 10:22:26
分享文章
本文介紹天翼云TeleDB數據庫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)
