Задать вопрос

digi54

Комментарии

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

    @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
    Написано