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)