Блог

Главная - Вопросы по программированию - postgres выбирает из поля json в качестве столбца для каждого ключа

postgres выбирает из поля json в качестве столбца для каждого ключа

#json #postgresql

#json #postgresql

Вопрос:

У меня есть таблица элементов (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
      }
   }
}

Я хотел бы выбрать и получить результаты fleetdetails следующим образом:

item.id Тип транспортного средства использование carBand
1 43572386-… Класс 3 N/A
1 fa999a90-… Класс 1 N/A и так далее.

Как я могу добиться этого, если я не знаю идентификаторов json fleetdetails?

Комментарии:

1. Пожалуйста, отправьте SQL-запрос, который вы пробовали

Ответ №1:

Комбинация 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

ДЕМОНСТРАЦИЯ

Ответ №2:

Вы можете использовать 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() вместо этого.