|
First, create a test table
CREATE TABLE job
(
jobid SERIAL primary key,
jobdesc json
)
Second, the selectors
-> (Returns an object type)
- >> (Return character)
# >> (Select array type)
Third, select insert update delete records
1. Insert Record
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 the Record
# Select jobdesc - >> 'jobname' as jobname from job where jobdesc -> 'objects' - >> 'wintest1' like 'oradb1';
jobname
------------------
oracle_tbs_space
(1 rows)
# Select jobdesc -> 'objects' as objects from job where jobdesc - >> 'jobname' = 'linux_os_vmstat';
objects
--------------------------
{+
"Wintest1": "cpu" +
}
(1 rows)
#select jobdesc -> 'values' # >> '{threshold, 0}' from job where jobdesc - >> 'jobname' = 'oracle_tbs_space';
Array element selection
# Select jobdesc -> 'values' # >> '{event, 0}' as value1 from job where jobdesc - >> 'jobname' = 'linux_os_vmstat';
value1
--------
cpu_r
(1 rows)
# Select jobdesc -> 'values' # >> '{event, 1}' as value2 from job where jobdesc - >> 'jobname' = 'linux_os_vmstat';
value2
--------
cpu_w
(1 rows)
2. Update record
#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 rows)
Must the entire field is updated when you update json type field can not be updated using the specified internal specific value method.
3. Delete Record
# 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 rows)
#
# 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 rows)
# |
|
|
|