如何将JSON数据导入到postgresql表中?

Question

我有以下JSON:

{
"ticket":{
"ticket":"61086762bb68d90001148fe9",
"timestamp":"2021-08-02T18:45:06.581-0300"
},
"serie":{
"measurement":{
"nemo":"RT",
"description":"Real Time"
},
"tagSet":{
"Granularity":"1h",
"Facility":"382"
},
"fieldSet":{
"EnvTemperature":"°C",
"HorizIrrad":"W/m²"
}
},
"values":[
{
"tagSet":{
"Facility":"382",
"Granularity":"1h"
},
"fieldSet":{
"EnvTemperature":7.0,
"HorizIrrad":632.0
},
"time":"2021-08-02T11:00:00.000-0300"
},
{
"tagSet":{
"Facility":"382",
"Granularity":"1h"
},
"fieldSet":{
"EnvTemperature":10.0,
"HorizIrrad":884.0
},
"time":"2021-08-02T12:00:00.000-0300"
}
]
}

我想知道如何导入"EnvTemperature"， "水平度"one_answers";time"从"值"一节开始到一个表中，像这样:

tbody> <<tr>

time	EnvTemperature	horizirad
2021-08-02 11:00:00	7	632
2021-08-02 12:00:00	10	884

使用函数jsonb_array_elements(jsonb)返回所有json数组元素作为value。

select 
(value->>'time')::timestamp as "time",
(value->'fieldSet'->>'EnvTemperature')::numeric as "EnvTemperature",
(value->'fieldSet'->>'HorizIrrad')::numeric as "HorizIrrad"
from the_data
cross join jsonb_array_elements(json_col->'values')

在Db<>fiddle中测试。

阅读JSON函数和操作符