在postgresql中将jsonb列中的数据转换为表

Question

我有一个表，它存储时间戳但它们在jsonb数组列中，像这样:

<表类> id 周期 tbody><<tr>5[{"以何种:"2022 - 10 - 18岁t18:31:34.529667z","时:"2022 - 10 - 05 - t19:01:51.400124z"},{时"开始":"2022 - 10 - 28 - t08:27:13.682084z"}]6[{时"开始":"2022 - 10 - 03 - t16:37:38.119236z"}]7[{"以何种:"2022 - 11 - 14 - t11:30:17.964960z","时:"2022 - 11 - 08 - t19:20:20.413133z"}]

您可以使用jsonb_to_recordset()

select ks.id, 
r.start, 
r.end
from ks
cross join jsonb_to_recordset(ks.cycles) as r("end" timestamptz, "start" timestamptz)      
order by ks.id, r.start  

适用于我将jsonb_path_query_array更改为jsonb_path_query的end值:

create table ks (id integer, cycles jsonb);
insert into ks values (5, '[{"end": "2022-10-18T18:31:34.529667Z", "start": "2022-10-05T19:01:51.400124Z"}, {"start": "2022-10-28T08:27:13.682084Z"}]'), (6, '[{"start": "2022-10-03T16:37:38.119236Z"}]'), (7, '[{"end": "2022-11-14T11:30:17.964960Z", "start": "2022-11-08T19:20:20.413133Z"}]');
select  
ks.id,  
jsonb_path_query(KS.cycles, '$.start') AS start,    
jsonb_path_query(KS.cycles, '$.end') as end  
from    
ks  ;
id |             start             |              end              
----+-------------------------------+-------------------------------
5 | "2022-10-05T19:01:51.400124Z" | "2022-10-18T18:31:34.529667Z"
5 | "2022-10-28T08:27:13.682084Z" | NULL
6 | "2022-10-03T16:37:38.119236Z" | NULL
7 | "2022-11-08T19:20:20.413133Z" | "2022-11-14T11:30:17.964960Z"

你的原始查询也可以:

select  
ks.id,  
jsonb_path_query(KS.cycles, '$.start') AS start,    
jsonb_path_query_array(KS.cycles, '$[*].start') as end  
from    
ks  ;
id |             start             |                              end                               
----+-------------------------------+----------------------------------------------------------------
5 | "2022-10-05T19:01:51.400124Z" | ["2022-10-05T19:01:51.400124Z", "2022-10-28T08:27:13.682084Z"]
5 | "2022-10-28T08:27:13.682084Z" | ["2022-10-05T19:01:51.400124Z", "2022-10-28T08:27:13.682084Z"]
6 | "2022-10-03T16:37:38.119236Z" | ["2022-10-03T16:37:38.119236Z"]
7 | "2022-11-08T19:20:20.413133Z" | ["2022-11-08T19:20:20.413133Z"]