PostgreSQL9.3Beta1:JSON 功能增强

在 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 表并插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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)

备注:创建两张测试表,其中第一张为 json 表。

1.2 操作符 -> 介绍
操作符 -> 可以返回 json 类型的字段值,例如:

1
2
3
4
5
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 类型,例如:

1
2
3
4
5
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.4 操作符 #> 介绍
操作符 #> 返回 json 数据字段指定的元素,如下:

1
2
3
4
5
6
7
8
9
10
11
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)

JSON 函数介绍

2.1 json_each(json) 函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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)

2.2 json_each_text(json) 函数

1
2
3
4
5
6
7
francs=> select * from json_each_text((select name from test_json1 where id=1));
key | value
------+--------
col1 | 1
col2 | francs
col3 | male
(3 rows)

2.3 row_to_json 函数

1
2
3
4
5
6
7
8
9
10
11
12
13
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)

备注:这个函数在 9.2 就有,将结果集转换成 json,这里也记录下。

聚合函数 Json_agg(record)

最后介绍新增加的聚合函数 json_agg(record),此函数用来将结果集转换成 JSON 数组。

3.1 例1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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)

备注:JSON 函数还是比较复杂的,更多内容请参考手册。

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%