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"

},

"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 行记录)

#

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!