阅读 39

PostgreSQL_11_把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)
复制代码
关注下面的标签,发现更多相似文章
评论