Как распарсить любое jsonb поле в postgresql динамически?

Question

[Илья]

Распарсить операторами -> и ->> очень просто, но это надо вручную прописывать каждое поле, а можно ли написать универсальный парсер, который не будет зависеть от структуры json'a? Точнее сказать, я уверен что можно, приложив определенные усилия, но я так полагаю такой велосипед должен быть уже изобретён)

Т.е. на входе у нас есть таблица в которой есть jsonb поле с json'ом любой структуры, а на выходе получаем таблицу в которой столько полей(столбцов) сколько в этом json'е пар ключ-значение. Соответственно именем поля будет название ключа, а значением в этом поле - значение.

Comments

[Dimonchik]

все хорошо, а зачем его парсить-то?

[Илья]

dimonchik2013, ну, разные сценарии. например, закинуть результат во временную таблицу и по какому-то из полей сделать уже джойн другой таблицы, что невозможно сделать не распарсив. да или даже просто взглянуть на данные, разбитые нормально, а не всё в кучу в одном джейсоне.

[Александр Филиппенко]

Так данные не структурированные же.
Да и типы определить динамически вряд ли получится.
Для джоина нужно знать конкретное имя и тип поля, и желательно иметь индекс на нём.
В общем в любом случае приходим к ручному разбору. С помощью функций jsonb_to_recordset/jsonb_to_record/jsonb_populate_recordset/jsonb_populate_record это делать удобнее чем с помощью стрелок.
Для удобного просмотра можно создать VIEW.
Ещё полезными бывают генерируемые колонки.

[Илья]

Александр Филиппенко, ну, да, неструктурированные, но я не ограничиваю инструменты для решения) Кодогенерация, как вариант.
В использовании указанных функций удобства не вижу, честно говоря, или я не понял как их правильно использовать. Вот у меня есть таблица в которой jsonb поле идет наряду с другими так скажем обычными полями, и мне в результирующей таблице нужно увидеть как обычные поля, так и распарсенный json, который станет соответственно несколькими полями, вместо одного. Т.е. поле таблицы в эти функции не подставишь, вроде как...

[Александр Филиппенко]

Илья, да не подставишь, но их можно вывести рядом в селекте, а значит что можно создать представление или реальную таблицу с нужным видом. Хотя вариант со стрелками, данном случае, выглядит самым простым.

[digi54]

Посмотрите, возможно поможет чем-то, использовал несколько способов.

CREATE OR REPLACE FUNCTION pg_temp.Temp_Search(
  SourceJson json, -- Массив в формате json, в котором ищем атрибут с заданным именем
  KeyName text, -- Имя искомого атрибута
  KeyValue text -- Значение искомого атрибута
)
RETURNS json AS $$
DECLARE ret json;
BEGIN
        SELECT  Value INTO ret
        FROM    json_array_elements(SourceJson)
        WHERE   json_extract_path_text(Value, KeyName) = KeyValue;

        RETURN ret;
END;
$$  LANGUAGE plpgsql;

select
  sampletime::date,
  sample.Sampleid,
  qmcontr.name,
  qmcontr.id,
  sample.createdatetime::time(0),
 --qm.elementcode,
 mm.elementcode,

  sum(Au) Au,
  sum(Ag) Ag,
  sum(S) S,
  sum(C) C,
  sum(C_ОРГ) C_ОРГ,
  sum(SCN) SCN,
  sum(Fe) Fe,
  sum(ЦИАНИД) ЦИАНИД,
  sum(PH) PH,
  sum(WAD_CN) WAD_CN,
  sum(TOTAL_CN) TOTAL_CN,
  sum(NAOH) NAOH,
  sum(NACN) NACN,
  
  
  case when extract (hour from sample.createdatetime + interval '5 hour') between 0 and 12 then '1 смена' else '2 смена' end workShift
    
      --END AS shift
  
from qualitymanagement_sample sample
join  (select
  q.elementid,
  case when name = 'Au' then case when q.result is null then 0 else q.result end else 0 end Au,
    case when name = 'Ag' then case when q.result is null then 0 else q.result end else 0 end Ag,
 case when name = 'S' then case when q.result is null then 0 else q.result end else 0 end S,
 case when name = 'C' then case when q.result is null then 0 else q.result end else 0 end C,
 case when name = 'C_ОРГ' then case when q.result is null then 0 else q.result end else 0 end C_ОРГ,
 case when name = 'SCN' then case when q.result is null then 0 else q.result end else 0 end SCN,
 case when name = 'Fe' then case when q.result is null then 0 else q.result end else 0 end Fe,
 case when name = 'ЦИАНИД' then case when q.result is null then 0 else q.result end else 0 end ЦИАНИД,
 case when name = 'PH' then case when q.result is null then 0 else q.result end else 0 end PH,
 case when name = 'WAD_CN' then case when q.result is null then 0 else q.result end else 0 end WAD_CN,
 case when name = 'TOTAL_CN' then case when q.result is null then 0 else q.result end else 0 end TOTAL_CN,
 case when name = 'NAOH' then case when q.result is null then 0 else q.result end else 0 end NAOH,
 case when name = 'NACN' then case when q.result is null then 0 else q.result end else 0 end NACN,
  u.symbolrus uom
from ( select
  elementid,
  sampleid,
  --2 - floor((extract(hour from sampletime) - 7) / 12) work_ship, --- если work_ship равен 1 или 3, то это первая смена, иначе 2 вторая смена
  pg_temp.Temp_Search((pg_temp.Temp_Search(json_array_elements(qualityindicators :: json -> 'Elements')-> 'Attributes', 'Id', 'Indicator')::json#>>'{Value,Attributes}')::json, 'Id','Name'
             )::json#>>'{Value,Value}' qi,
--  json_array_elements(qualityindicators :: json -> 'Elements')-> 'Attributes' -> 4 -> 'Value' -> 'Attributes' -> 6 ->'Value'->>'Value' qi,
  (json_array_elements(qualityindicators :: json -> 'Elements')-> 'Attributes' -> 0 -> 'Value' ->> 'Value')::numeric result,
  json_array_elements(qualityindicators :: json -> 'Elements')-> 'Attributes'-> 3 ->'Value'->'Attributes'->7->'Value'->>'Value' uom
 from qualitymanagement_sample qs
 where samplestate='Approved'
   ) q

   join qmqualityindicators_qualityindicator a on q.qi = a.elementid

   left join uom_uom u on q.uom = u.elementid
     where
      a.name in
       ('S','C','C_ОРГ','SCN','Fe','ЦИАНИД','Au','PH','Ag','WAD_CN','TOTAL_CN','NAOH','NACN')
            and (u.symbolrus in ('мг/дм3', '%','г/дм3', '-') or (u.symbolrus is null))

 ) q on q.elementid=sample.elementid
JOIN
(SELECT distinct
  --qmcontr.elementid,
  --qmcontr.id,
  --qmcontr.name,
  --qmcontr.elementcode,
  ssample.orderid,
  ssample.sampleid
  FROM
  qmcontrolpoints_controlpoint qmcontr
     join material_materialcollectionlinks mcl on qmcontr.materials=mcl.collection_id
     join qmmaterials_material qm on qm.elementid=mcl.element_reference
     join qualitymanagement_sample ssample on qmcontr.elementid=ssample.controlpoint
   where qmcontr.id
    in ('VSP000073', 'VSP000062', 'VSP000060', 'VSP000052', 'VSP000059', 'VSP000058','VSP000034')
    and ssample.samplestate ='Approved'
    and qm.elementcode
     in ('Питание флотации. жидкая фаза (1049480)',
      'Оборотная вода (1039709)',
      'Хвосты сорбции, жидкая фаза (1028308)',
      'Хвосты флотации, жидкая фаза (1039903)',
      'Слив сгущения (1039856)',
    'Хвосты сорбции обезвреженные. жидкая фаза (1049585)',
      'Жидкая фаза хвостохранилища хвостов сорбции (1049599)') ) ssample on ssample.sampleid=sample.Sampleid


  join qmcontrolpoints_controlpoint qmcontr on qmcontr.elementid=sample.controlpoint
  --join material_materialcollectionlinks mcl on qmcontr.materials=mcl.collection_id
  JOIN material_material as mm ON mm.elementid = sample.material
-- join qmmaterials_material qm on qm.elementid=mcl.element_reference
--where  sample.createdatetime >= @creationtime and sample.createdatetime <= @expirationtime --and  sample.Sampleid = 'MES000004725' or sample.Sampleid ='MES000004721'
group by sampletime::date, sample.Sampleid, qmcontr.name, qmcontr.id, sample.createdatetime, mm.elementcode