PostgreSQL把SQL查询结果转换为JSON类型
背景
数据仓库在DWS层对数据进行汇总的时候，经常会出现数据一对多的情况。这个时候会使用父子表的形式存储数据。但是在构造Data API的过程中，往往会使用JSON格式，把多条子信息整合到父信息上，最终形成一条JSON Document。
传统的做法是，使用Python，Java等程序语言对父子数据进行查询，然后在中间层转换封装，构造对应的JSON数据。
因为PG对JSON有很完善的支持，所以我们可以采用另一种方案，直接在父表里面增加JSONB格式的字段。这种方案在ETL过程中进行最终数据构造，隐藏了查询过程的复杂性，一次构造，多次使用。
参考 PostgreSQL 11 官方 API 文档 : JSON Functions and Operators
构造原始数据
我们模拟一个场景
- 父表是车辆基础信息表，一辆车有唯一的一条记录；
- 子表是车辆运输过的线路统计，一辆车加一条线路有唯一的一条记录；
- 最终返回JSON对象，一辆车一条数据，线路信息作为子元素嵌套；
-- 车辆表
drop table if exists t_2js_truck;
create table demo.t_2js_truck (id int, vehicle_no varchar(50), update_time timestamp) ;
insert into demo.t_2js_truck values(1, 'A001', '2019-01-01 01:02:03');
insert into demo.t_2js_truck values(2, 'A002', '2019-01-02 01:02:03');
-- 线路表，逻辑外键是 truck_id
drop table if exists t_2js_truck_route;
create table demo.t_2js_truck_route (id int, truck_id int, route_from varchar(50), route_to varchar(50), cnt int);
insert into demo.t_2js_truck_route values(10, 1, '四川省成都市', '云南省昆明市', 9);
insert into demo.t_2js_truck_route values(11, 1, '四川省广汉市', '云南省昆明市', 5);
insert into demo.t_2js_truck_route values(12, 1, '陕西省西安市', '四川省广汉市', 2);
DB2JSON场景
单表单行转JSON对象
使用row_to_json()和jsonb_build_object()把查询结果集一比一转换成JSON
-- row_to_json() 必须把所有字段都转换
select row_to_json(t.*) as json_obj from demo.t_2js_truck t;
-- jsonb_build_object() 有选择的转换一部分字段
select jsonb_build_object('id', t.id, 'vehicle_no', t.vehicle_no, 'update_time', t.update_time ) as json_obj from demo.t_2js_truck t;
-- 上面两条SQL都转换了所有字段，所以结果是一样的
{"id":1,"vehicle_no":"A001","update_time":"2019-01-01T01:02:03"}
{"id":2,"vehicle_no":"A002","update_time":"2019-01-02T01:02:03"}
(2 rows)
单表多行转JSON数组
我们需要把线路表中同一车辆的所有线路信息，转换成一个json数组。结合使用jsonb_agg()和jsonb_build_object()实现。
-- 多条记录整合成 json array
SELECT
jsonb_agg (
jsonb_build_object ( 'route_from', r.route_from, 'route_to', r.route_to, 'cnt', r.cnt )
) as json_arr
FROM
demo.t_2js_truck_route r;
[{"route_from" : "四川省成都市", "route_to" : "云南省昆明市", "cnt" : 9}, {"route_from" : "四川省广汉市", "route_to" : "云南省昆明市", "cnt" : 5}, {"route_from" : "陕西省西安市", "route_to" : "四川省广汉市", "cnt" : 2}]
(1 row)
子表多行转主表JSON字段
我们需要把子表中记录的多条线路信息，集中整合到对应的车辆信息中。这个案例里面，我们保持数据集的表格结构，仅仅是把线路信息作为表格的一个json字段。
-- 通过嵌套子查询，把车辆信息和多条线路信息整合成一条记录
-- 结果格式是表格，只是带有json格式的字段
-- coalesce()保证了即使没有对应的线路信息，也会生成一个空数组
select
t.id, t.vehicle_no, t.update_time,
(select coalesce(jsonb_agg(json_build_object('route_from', r.route_from, 'route_to', r.route_to, 'cnt', r.cnt)) , '[]')
from demo.t_2js_truck_route r where r.truck_id= t.id
) as routes
from demo.t_2js_truck t ;
id | vehicle_no | update_time | routes
1 | A001 | 2019-01-01 01:02:03 | [{"cnt": 9, "route_to": "云南省昆明市", "route_from": "四川省成都市"}, {"cnt": 5, "route_to": "云南省昆明市", "route_from": "四川省广汉市"}, {"cnt": 2, "route_to": "四川省广汉市", "route_from": "陕西省西安市"}]
2 | A002 | 2019-01-02 01:02:03 | []
(2 rows)
主表子表整合转JSON对象
上一个场景中，我们最后得到的结果是表格结构，但在对外提供数据的时候需要使用更方便的JSON对象。使用jsonb_build_object()对刚才的结果做一些简单的调整即可。
-- 通过嵌套子查询，把车辆信息和线路信息整合成一条记录
-- 结果格式json对象
select
jsonb_build_object(
'id', t.id, 'vehicle_no', t.vehicle_no, 'update_time', t.update_time,
'routes' , (select coalesce(jsonb_agg(jsonb_build_object('route_from', r.route_from, 'route_to', r.route_to, 'cnt', r.cnt)) , '[]')
from demo.t_2js_truck_route r where r.truck_id= t.id )
) as json_obj
from demo.t_2js_truck t
;
{"id": 1, "routes": [{"cnt": 9, "route_to": "云南省昆明市", "route_from": "四川省成都市"}, {"cnt": 5, "route_to": "云南省昆明市", "route_from": "四川省广汉市"}, {"cnt": 2, "route_to": "四川省广汉市", "route_from": "陕西省西安市"}], "vehicle_no": "A001", "update_time": "2019-01-01T01:02:03"}
{"id": 2, "routes": [], "vehicle_no": "A002", "update_time": "2019-01-02T01:02:03"}
(2 rows)
JSON2DB场景
如果PG中存储了嵌套JSON格式的数据，我们也可以转换成标准的表格结构。
drop table if exists demo.t_2js_truck_stat;
create table demo.t_2js_truck_stat (id int, vehicle_no varchar(50), update_time timestamp, routes jsonb) ;
-- 我们把刚才查询的结果保存到表t_2js_truck_stat里面
insert into demo.t_2js_truck_stat
select
t.id, t.vehicle_no, t.update_time,
(select coalesce(jsonb_agg(jsonb_build_object('route_from', r.route_from, 'route_to', r.route_to, 'cnt', r.cnt)) , '[]')
from demo.t_2js_truck_route r where r.truck_id= t.id
) as routes
from demo.t_2js_truck t
;
拆分JSON数组为多行
使用jsonb_array_elements()来把json列表拆分成多条数据，并且可以和非json字段的内容进行类似于join的操作。比如下面的例子，我们模拟了车辆A001关联对应的三条线路数据。
select ts.id, ts.vehicle_no,
jsonb_array_elements(ts.routes) as route
from demo.t_2js_truck_stat ts ;
复制代码
id | vehicle_no | route
----+------------+----------------------------------------------------------------------
1 | A001 | {"cnt": 9, "route_to": "云南省昆明市", "route_from": "四川省成都市"}
1 | A001 | {"cnt": 5, "route_to": "云南省昆明市", "route_from": "四川省广汉市"}
1 | A001 | {"cnt": 2, "route_to": "四川省广汉市", "route_from": "陕西省西安市"}
(3 rows)
注意：这种方式只能模拟inner join。如上图结果中，因为车辆A002的routes字段json array为空，所以最后结果中没有显示A002的数据。
获取JSON内容
使用 -> 和 ->> 直接把json字段内容取出来
-- 更进一步，使用表格返回全部字段
select ts.id, ts.vehicle_no,
(jsonb_array_elements(ts.routes)->'cnt')::int as cnt ,
jsonb_array_elements(ts.routes)->>'route_from' as route_from ,
jsonb_array_elements(ts.routes)->>'route_to' as route_to
from demo.t_2js_truck_stat ts ;
id | vehicle_no | cnt | route_from | route_to
----+------------+-----+--------------+--------------
1 | A001 | 9 | 四川省成都市 | 云南省昆明市
1 | A001 | 5 | 四川省广汉市 | 云南省昆明市
1 | A001 | 2 | 陕西省西安市 | 四川省广汉市
(3 rows)
评论