postgres使用聚合函数json_agg()实现行专列

问题：将下图中的数据实现行专列

 实现：1、使用json_agg(json_build_object(xxx))聚合函数转换成[{"xxx":"xxx"},{"xxx":"xxx"}]格式数据。

select 
t.instance_id,json_agg(hson_build_object(
    t.act_id || '_act_id',t.act_id,
    t.act_id || '_node_name',t.node_name,
    t.act_id || '_time',t.time
)
order by t.time
) as json_arr
from 
table as t
group by t.instance_id

2、得到结果为：

[{"zhangsan_act_id":"zhangsan","zhangsan_node_name":"张三","zhangsan_time":"2023-08-16 16:30:00"},
{"lisi_act_id":"lisi","lisi_node_name":"李四",";lisi_time":"2023-08-16 16:30:01"},
{"wangwu_act_id":"wangwu","wangwu_node_name":"王五","wangwu_time":"2023-08-16 16:30:02"},
{"mazi_act_id":"mazi","mazi_node_name":"麻子","mazi_time":"2023-08-16 16:30:03"}]

 3、使用jsonb_array_element(jsonb,integer)聚合函数获取数组中的json数据(这里的t1为第一个图中的SQL)。

select
jsonb_array_element(jsonb(t1.json_arr),0) ->> 'zhangsan_node_name' as zhangsan_node_name,
jsonb_array_element(jsonb(t1.json_arr),1) ->> 'lisi_node_name' as lisi_node_name,
from t1

4、得到的结果