postgresql 之row_to_json

 

 

           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)