一、创建测试表
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":{
"data":["cpu_r"],
"threshold":[1,1]
},
"objects":{
"wintest1":"cpu"
}
}');
insert into job(jobdesc) values('{
"jobname":"oracle_tbs_space",
"schedule":{
"type":{"interval":
"1d"
},
"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",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" +
| },+
| "values":{ +
| "event":["cpu_r",+
| "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" +
| },+
| "threshold":["90%"] +
| },+
| "objects":{ +
| "wintest1":"oradb1" +
| } +
| }
(1 行记录)
#
