PostgreSQL9.3：JSON 功能增强 根据PQ中文论坛francs 给出的东西结合自己的摸索总结下
在 PostgreSQL 9.2 版本中已经支持 JSON 类型，不过支持的操作非常有限，仅支持以下函数
array_to_json(anyarray [, pretty_bool])
row_to_json(record [, pretty_bool])
如果想扩充 JSON 其它函数，得另外安装一些外部模块，好在 9.3 版本 JSON 增加了多种函数
和操作符，这增强了 JSON 的功能，接下来演示下。
一 JSON 操作符介绍
--1.1 创建 json 表并插入数据
| francs=> create table test_json1 (id serial primary key,name json);
CREATE TABLE
francs=> insert into test_json1 (name) values ('{"col1":1,"col2":"francs","col3":"male"}');
INSERT 0 1
francs=> insert into test_json1 (name) values ('{"col1":2,"col2":"fp","col3":"female"}');
INSERT 0 1
francs=> select * From test_json1;
id | name
----+------------------------------------------
1 | {"col1":1,"col2":"francs","col3":"male"}
2 | {"col1":2,"col2":"fp","col3":"female"}
(2 rows)
francs=> create table test_1 (id int4,name varchar(32),flag char(1));
CREATE TABLE
francs=> insert into test_1 values (1,'a','f');
INSERT 0 1
francs=> insert into test_1 values (2,'b','f');
INSERT 0 1
francs=> insert into test_1 values (3,'c','t');
INSERT 0 1
francs=> select * from test_1;
id | name | flag
----+------+------
1 | a | f
2 | b | f
3 | c | t
(3 rows)
|
--1.2 操作符 -> 介绍
操作符 -> 可以返回 json 类型的字段值，例如：
| francs=> select id ,name ->'col1' col1, name -> 'col2' col2, name -> 'col3' col3 from test_json1 where id=1;
id | col1 | col2 | col3
----+------+----------+--------
1 | 1 | "francs" | "male"
(1 row)
|
--1.3 操作符 ->> 介绍
操作符 ->> 与之前的 -> 类似，只不过返回的是 text 类型，例如：
| francs=> select id ,name ->>'col1' col1, name ->> 'col2' col2, name ->> 'col3' col3 from test_json1 where id=1;
id | col1 | col2 | col3
----+------+--------+------
1 | 1 | francs | male
(1 row)
|
以上2个例子说明了json数据可以被以表的形式显示出来，这对于query的查询很有帮助，因为现在的开发语言都不是直接支持json的，所以到前台之后需要读取转换，这样很麻烦，有了这个函数前台的query就
直接可以用了，PQ帮你做好了一切
这里例1的返回结果是类型不变的结果，这里有个问题就是"":"" text类型 会返回带冒号的结果，这样在实际中可能会困扰，但是可以保持数据的原始类型，
对于个人而言，我的数据基本都是text类型，实际用的时候在转换，为了统一，而PQ也给力方案例2中就是以text输出的，这样text类型的冒号（""）就没有了
--1.4 操作符 #> 介绍
操作符 #> 返回 json 数据字段指定的元素，如下：
| francs=> select '{"a":[1,2,3],"b":[4,5,6]}'::json#>'{b,1}';
?column?
----------
5
(1 row)
francs=> select '{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}';
?column?
----------
3
(1 row)
|
这里我说一个扩展，可能我自己比较笨所以反反复复实验了，说下另外的应用
如以上例子中的表1中 用以上函数就可以查询制定的值
select id ,name #> '{col2}' from test_json1 where id=1;
?column?
----------
"francs"
同理，返回的是源类型，自己琢磨了下，可以返回text类型
select id ,name #>> '{col2}' from test_json1 where id=1;
?column?
----------
francs
哈哈，很神奇
二 JSON 函数介绍
--2.1 json_each(json) 函数
| francs=> select * from test_json1 where id=1;
id | name
----+------------------------------------------
1 | {"col1":1,"col2":"francs","col3":"male"}
(1 row)
francs=> select * from json_each((select name from test_json1 where id=1));
key | value
------+----------
col1 | 1
col2 | "francs"
col3 | "male"
(3 rows)
francs=> select * from json_each('{"a":"foo", "b":"bar"}');
key | value
-----+-------
a | "foo"
b | "bar"
(2 rows)
|
这个函数就是返回原始数据类型为一个表，可以看到text类型有""号
--2.2 json_each_text(json) 函数
| francs=> select * from json_each_text((select name from test_json1 where id=1));
key | value
------+--------
col1 | 1
col2 | francs
col3 | male
(3 rows)
|
这个不用说了，返回text类型
--2.3 row_to_json 函数
| francs=> select row_to_json(test_1) from test_1;
row_to_json
--------------------------------
{"id":1,"name":"a","flag":"f"}
{"id":2,"name":"b","flag":"f"}
{"id":3,"name":"c","flag":"t"}
(3 rows)
francs=> select row_to_json(test_1) from test_1 where id=1;
row_to_json
--------------------------------
{"id":1,"name":"a","flag":"f"}
(1 row)
|
这个原来我只会as的方式，没想到可以这样
备注：这个函数在 9.2 就有，将结果集转换成 json，这里也记录下。
三 聚合函数 json_agg(record)
最后介绍新增加的聚合函数 array_agg(record)，此函数用来将结果集转换成 JSON 数组。
--3.1 例1
| francs=> alter table test_json1 add grade int4 default '6';
ALTER TABLE
francs=> select * from test_json1;
id | name | grade
----+------------------------------------------+-------
1 | {"col1":1,"col2":"francs","col3":"male"} | 6
2 | {"col1":2,"col2":"fp","col3":"female"} | 6
(2 rows)
francs=> select json_agg(name) from test_json1 group by grade;
json_agg
------------------------------------------------------------------------------------
[{"col1":1,"col2":"francs","col3":"male"}, {"col1":2,"col2":"fp","col3":"female"}]
(1 row)
|
--3.2 例2
| francs=> select * from test_1;
id | name | flag
----+------+------
1 | a | f
2 | b | f
3 | c | t
(3 rows)
francs=> select json_agg(a) from test_1 a;
json_agg
-----------------------------------
[{"id":1,"name":"a","flag":"f"}, +
{"id":2,"name":"b","flag":"f"}, +
{"id":3,"name":"c","flag":"t"}]
(1 row)
|
聚合函数就更牛牛逼了，以前row_to_json的时候基本都是只能一串一串输出，在用代码拼接，这下好了，PQ帮你直接连接起来，而且保证了数据绝对的准确性，同样也可以结合上面的符号
select json_agg(a) #>>'{0,name}' from test_1 a;
备注：JSON 函数还是比较复杂的，更多内容请参考手册。
