PostgreSQL 9.3.2 Json类型使用

一、创建测试表
CREATE TABLE job
（
jobid SERIAL primary key ,
jobdesc json
）
二、选择符
->（返回对象类型）
->>（返回字符型）
#>>（选择数组类型）
三、插入选择更新删除操作记录
1.插入记录
insert into job（jobdesc） values（"{
"jobname":"linux_os_vmstat",
"schedule":{
"type":{"interval":
"5m"
},
"start":"now",
"end":"None"
},
"values":{
"event":["cpu_r","cpu_w"],
"data":["cpu_r"],
"threshold":[1,1]
},
"objects":{
"wintest1":"cpu"
}
}"）;
insert into job（jobdesc） values（"{
"jobname":"Oracle_tbs_space",
"schedule":{
"type":{"interval":
"1d"
},
"start":"now",
"end":"None"
},
"values":{
"event":["used"],
"data":["used"],
"threshold":["90%"]
},
"objects":{
"wintest1":"oradb1"
}
}"）;
2.选择记录
# select jobdesc->>"jobname" as jobname from job where jobdesc->"objects"->>"wintest1" like "oradb1";
jobname
------------------
oracle_tbs_space
（1 行记录）
# select jobdesc->"objects" as objects from job where jobdesc->>"jobname" = "linux_os_vmstat";
objects
--------------------------
{ +
"wintest1":"cpu"+
}
（1 行记录）
#select jobdesc->"values"#>>"{threshold,0}" from job where jobdesc->>"jobname" = "oracle_tbs_space";
数组元素选择
# select jobdesc->"values"#>>"{event,0}" as value1 from job where jobdesc->>"jobname" = "linux_os_vmstat";
value1
--------
cpu_r
（1 行记录）
# select jobdesc->"values"#>>"{event,1}" as value2 from job where jobdesc->>"jobname" = "linux_os_vmstat";
value2
--------
cpu_w
（1 行记录）
2.更新记录
#update job set jobdesc = "{
"jobname":"linux_os_vmstat",
"schedule":{
"type":{"interval":
"5m"
},
"start":"now",
"end":"None"
},
"values":{
"event":["cpu_r","cpu_w"],
"data":["cpu_r"],
"threshold":[1,2]
},
"objects":{
"wintest1":"cpu"
}
}" where jobdesc->>"jobname" = "linux_os_vmstat";
UPDATE 1
# select jobdesc->"values"#>>"{threshold,1}" as threshold2 from job
where jobdesc->>"jobname" = "linux_os_vmstat";
threshold2
------------
2
（1 行记录）
更新json类型字段时必须整个字段都更新，无法采用指定内部特定值方法更新。
3.删除记录
# select * from job;
jobid | jobdesc
-------+------------------------------------
3 | { +
| "jobname":"oracle_tbs_space", +
| "schedule":{ +
| "type":{"interval": +
| "1d" +
| }, +
| "start":"now", +
| "end":"None" +
| }, +
| "values":{ +
| "event":["used"], +
| "data":["used"], +
| "threshold":["90%"] +
| }, +
| "objects":{ +
| "wintest1":"oradb1" +
| } +
| }
4 | { +
| "jobname":"linux_os_vmstat", +
| "schedule":{ +
| "type":{"interval": +
| "5m" +
| }, +
| "start":"now", +
| "end":"None" +
| }, +
| "values":{ +
| "event":["cpu_r","cpu_w"],+
| "data":["cpu_r"], +
| "threshold":[1,1] +
| }, +
| "objects":{ +
| "wintest1":"cpu" +
| } +
| }
（2 行记录）
#
# delete from job where jobdesc->>"jobname" = "linux_os_vmstat";
DELETE 1
# select * from job;
jobid | jobdesc
-------+-----------------------------------
3 | { +
| "jobname":"oracle_tbs_space",+
| "schedule":{ +
| "type":{"interval": +
| "1d" +
| }, +
| "start":"now", +
| "end":"None" +
| }, +
| "values":{ +
| "event":["used"], +
| "data":["used"], +
| "threshold":["90%"] +
| }, +
| "objects":{ +
| "wintest1":"oradb1" +
| } +
| }
（1 行记录）
#PostgreSQL 的详细介绍：请点这里
PostgreSQL 的下载地址：请点这里PostgreSQL缓存详述 http://www.linuxidc.com/Linux/2013-07/87778.htmWindows平台编译 PostgreSQL http://www.linuxidc.com/Linux/2013-05/85114.htmUbuntu下LAPP（Linux+Apache+PostgreSQL+PHP）环境的配置与安装 http://www.linuxidc.com/Linux/2013-04/83564.htmUbuntu上的phppgAdmin安装及配置 http://www.linuxidc.com/Linux/2011-08/40520.htmCentOS 6.5_x64安装Oracle 11g R2PostgreSQL Stream 配置相关资讯 PostgreSQL 9.3.2 Json使用本文评论查看全部评论（0）