1. 云图网首页
  2. 技术专区
  3. 数据库

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 函数还是比较复杂的，更多内容请参考手册。

参考

原创文章，作者：carmelaweatherly，如若转载，请注明出处：https://blog.ytso.com/237961.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论