PostgreSQL 9.2新增加了一个新的特性--JSON支持,具体包含一个JSON数据类型和两个json函数,这些techniques可以从数据库直接获取json格式的数据
How To
vkott_livecde=# select * from live_area_info ;
area_id | area_name | description
---------+------------+------------------------
0 | 采集零片区 | 采集没有片区,使用0片区
1 | 北美片区 | 给北美地区用户的片区
2 | 东南亚片区 | 给东南亚地区用户的片区
3 | 欧洲片区 | 给欧洲地区用户的片区
4 | 南美片区 | 给南美地区用户的片区
(5 rows)
vkott_livecde=# select row_to_json(live_area_info) from live_area_info;
row_to_json
-------------------------------------------------------------------------------
{"area_id":0,"area_name":"采集零片区","description":"采集没有片区,使用0片区"}
{"area_id":1,"area_name":"北美片区","description":"给北美地区用户的片区"}
{"area_id":2,"area_name":"东南亚片区","description":"给东南亚地区用户的片区"}
{"area_id":3,"area_name":"欧洲片区","description":"给欧洲地区用户的片区"}
{"area_id":4,"area_name":"南美片区","description":"给南美地区用户的片区"}
(5 rows)
有时候,我们并不希望包括某些特定的字段,这种情况下可以用row构造方法:
vkott_livecde=# select row_to_json(row(area_id,description)) from live_area_info;
row_to_json
----------------------------------------
{"f1":0,"f2":"采集没有片区,使用0片区"}
{"f1":1,"f2":"给北美地区用户的片区"}
{"f1":2,"f2":"给东南亚地区用户的片区"}
{"f1":3,"f2":"给欧洲地区用户的片区"}
{"f1":4,"f2":"给南美地区用户的片区"}
(5 rows)
从结果可以看出,仅仅是获取了area_id和description字段,但是失去了字段名,全都是以f1,f2...来代替。
解决这个问题,必须采用子查询的办法:
vkott_livecde=# select row_to_json(t)
from (
select area_id,description from live_area_info
) t
;
row_to_json
------------------------------------------------------
{"area_id":0,"description":"采集没有片区,使用0片区"}
{"area_id":1,"description":"给北美地区用户的片区"}
{"area_id":2,"description":"给东南亚地区用户的片区"}
{"area_id":3,"description":"给欧洲地区用户的片区"}
{"area_id":4,"description":"给南美地区用户的片区"}
(5 rows)
其他经常使用的techniques是array_agg和array_to_json,array_agg是一个类似于sum和count的统计函数,array_to_json则是返回一个postgresql数组所对应的json对象:
vkott_livecde=# select array_to_json(array_agg(row_to_json(t)))
from (
select area_id,description from live_area_info
) t
;
array_to_json
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------
[{"area_id":0,"description":"采集没有片区,使用0片区"},{"area_id":1,"description":"给北美地区用户的片区"},{"area_id":2,"description":"给东南亚地区用户的片区"},{"area_id":3,"description":"给
洲地区用户的片区"},{"area_id":4,"description":"给南美地区用户的片区"}]
(1 row)
组合使用的话,可以返回更复杂的数据结构:
vkott_livecde=# select row_to_json(t)
vkott_livecde-# from (
vkott_livecde(# select node_ip, description,
vkott_livecde(# (
vkott_livecde(# select array_to_json(array_agg(row_to_json(d)))
vkott_livecde(# from (
vkott_livecde(# select area_name, description
vkott_livecde(# from live_area_info
vkott_livecde(# where area_id=live_trans_node_info.area_id
vkott_livecde(# order by node_ip asc
vkott_livecde(# ) d
vkott_livecde(# ) as area_info
vkott_livecde(# from live_trans_node_info
vkott_livecde(# where node_ip = '53.18.23.121'
vkott_livecde(# ) t;
row_to_json
---------------------------------------------------------------------------------------------------------------------------------------------------------
{"node_ip":"53.18.23.121","description":"部署在澳洲悉尼机房的缓存结点","area_info":[{"area_name":"采集零片区","description":"采集没有片区,使用0片区"}]}
(1 row)
About json_populate*
json_populate_record的作用是按照第一个参数提供的列名,依次对比后一个json对象,未匹配的成员以逗号代替。
vktest=# SELECT json_populate_record(NULL:: terminal.terminal,'{"user_id":"f03d9b05-9a49-49a9-9b59-d1252b9efaef","register_date":"2018-06-02T05:42:09.44085","expiration":"2020-12-02T05:42:09.44085"}') AS r;
r
-------------------------------------------------------------------
(,,,,,,"2018-06-02 05:42:09.44085",,,"2020-12-02 05:42:09.44085")
vkott_livecde=# drop type if exists json_test_columns;
NOTICE: type "json_test_columns" does not exist, skipping
DROP TYPE
vkott_livecde=# create type json_test_columns as (a int,b int,c int,d int);
CREATE TYPE
vkott_livecde=# select * from json_populate_record(null::json_test_columns,'{"a":1,"b":2,"c":3,"d":4}');
a | b | c | d
---+---+---+---
1 | 2 | 3 | 4
(1 row)
vkott_livecde=# select * from json_populate_recordset(null::json_test_columns,'[{"a":1,"b":2,"c":3,"d":4},{"a":2,"b":3,"c":4,"d":5}]');
a | b | c | d
---+---+---+---
1 | 2 | 3 | 4
2 | 3 | 4 | 5
(2 rows)
vkott_livecde=# select value->>0 as a,value->>1 as b,value->>2 as c,value->>3 as c from (select * from jsonb_array_elements('[[1,2,3,4],[2,3,4,5]]')) as tmp;
a | b | c | c
---+---+---+---
1 | 2 | 3 | 4
2 | 3 | 4 | 5
(2 rows)