postgres select from json field as column for each key

Question

I have Items (id, data (json)) table. Data column structure is this:

   "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
      }
   }
}

I would like to select and get fleetdetails results like this:

item.id	vehicleType	usage	carBand
1	43572386-...	Class 3	N/A
1	fa999a90-...	Class 1	N/A	and so on.

How I can achieve this if I don't know fleetdetails json ids?

Please post the SQL query you tried

Bergi
– Bergi

2020-12-08 11:37:13 +00:00
Commented Dec 8, 2020 at 11:37 — Bergi
– Bergi, Commented Dec 8, 2020 at 11:37

Akhilesh Mishra · Accepted Answer · 2020-12-08 11:59:15Z

1

Combination of JSONB_EACH() and CROSS JOIN LATERAL will do the things for you try below query:


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

DEMO

answered Dec 8, 2020 at 11:59

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2020-12-08 11:55:17Z

1

You can use 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);

This assumes that the column data is defined as jsonb (which it should be). If it's just a json column you have to use json_each() instead.

answered Dec 8, 2020 at 11:55

user330315

Collectives™ on Stack Overflow

postgres select from json field as column for each key

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related