postgres从json字段中选择作为每个键的列

Question

我有Items(id，data(json((表。数据列结构如下：

"somethingNotImportant":{
"someName":"Its a name",
"someProduct":"its a product"
},
"anotherNotImportant":{
"installments":null,
"baseRate":"30",
"grossComm":"20",
"totalMileage":null
},
"fleetdetails":{
"4b4bd441-a8eb-4754-9384-6f97d1ee23e3":{
"vehicleType":"43572386-5908-4e46-bf2b-3948df2e0e72",
"usage":"Class 3",
"carBand":"N/A",
"coverType":"Third-Party Only",
"vehicleNumber":"1",
"modelRate":"222",
"technicalRate":"333",
"annualMileage":"444",
"adftExcess":"555",
"wsExcess":"777",
"annualBasePremium":null,
"usageRate":null
},
"cc12cc77-9346-4cae-8c27-6afc8b457f9b":{
"vehicleType":"fa999a90-b98f-499a-bef6-55b9a208c2fc",
"usage":"Class 1",
"carBand":"N/A",
"coverType":"Comprehensive",
"vehicleNumber":"1",
"modelRate":"2",
"technicalRate":"3",
"annualMileage":"4",
"adftExcess":"5",
"wsExcess":"6",
"annualBasePremium":null,
"usageRate":null
}
}
}

我想选择并获得这样的快速详细信息结果：

使用依此类推。

item.id	车辆类型	汽车波段
1	43572386-	类别3
1	fa999a90-	类别1	N/A

JSONB_EACH()和CROSS JOIN LATERAL的组合将为您完成以下查询：

select 
t1.id "id",
t2.value->>'vehicleType' "VEHICLETYPE",
t2.value->>'usage' "usage",
t2.value->>'carBand' "carBand",
t2.value->>'coverType' "coverType",
t2.value->>'vehicleNumber' "vehicleNumber",
t2.value->>'modelRate' "modelRate",
t2.value->>'technicalRate' "technicalRate",
t2.value->>'annualMileage' "annualMileage",
t2.value->>'adftExcess' "adftExcess",
t2.value->>'wsExcess' "wsExcess",
t2.value->>'annualBasePremium' "annualBasePremium",
t2.value->>'usageRate' "usageRate"
from items t1 cross join lateral jsonb_each(data->'fleetdetails') t2

演示

您可以使用jsonb_each():

select i.id, 
d.detail ->> 'vehicleType' as vehicle_type,
d.detail ->> 'usage' as usage, 
d.detail ->> 'carBand' as car_band 
from item i
cross join jsonb_each(i.data -> 'fleetdetails') as d(key, detail);

这假设列data被定义为jsonb(它应该是(。如果它只是一个json列，则必须使用json_each()。