Блог

У меня есть запрос, где вывод двух строк

     select 
    specimen_id,
    jsonb_agg(result) as test_result 
    from (
    select 
        code,
        first_value(split_part(id, '^', 5)) OVER (PARTITION BY group_id ORDER BY index) as specimen_id,
        json_build_object( 
        'resultId',
         value,
         'resultValue',
         replace(reading,'*','')
         ) as result
         from (
        SELECT
          parts[1] as code,
          parts[5] as id,
          parts[6] as reading,
        split_part(parts[4], '^', 2) as value,
        elem.index,
        SUM((parts[1] = 'OBR')::int) OVER (ORDER BY elem.index) as group_id
            FROM  unnest (string_to_array('<ENQ><VT>MSH|^~amp;|Icon-3|NI30H20107  |LIS Application|LIS|20210321001054||ORU^R01|637518822544590000|P|2.5||||||UNICODE UTF-8<CR><LF>SFT|ACTIVE SCIENTIFIC SDN BHD|1.3.1563.0|Icon-3|1.3.1563.0|Message Version: Version_0_2_437 Software complete version: 1.3.1563.0(FE - 00 - 45)|20200811032246<CR><LF>OBR||||^^^5082^2107000243||||20210321001047|25||||3 Part Differential Hematology<CR><LF>NTE|Comment1||SITI JUMAANI|1^Name<CR><LF>NTE|Comment2||MAEPS101823|2^IC                            <CR><LF>NTE|Comment3||HALL D1|3^HALL                          <CR><LF>NTE|Comment4|||4^RBC flags<CR><LF>NTE|Comment5|||5^PLT flags<CR><LF>NTE|Comment6|||6^WBC flags<CR><LF>NTE|Comment7|||7^Technical flags<CR><LF>NTE|Comment9||91715980620_00124|9^Reagent Diluent<CR><LF>NTE|Comment10||91715980620_00124|10^Reagent System Solution<CR><LF>NTE|Comment11||91715980620_00124|11^Reagent Lyse<CR><LF>OBX|||0^RBC||4.99|^10â¶/μL|3.85-5.78||||F<CR><LF>OBX|||1^HGB||15.1|^g/dL|12.0-17.2||||F<CR><LF>OBX|||2^MCV||91.0|^fL|78.0-96.0||||F<CR><LF>OBX|||3^HCT||45.4|^%|34.8-50.9||||F<CR><LF>OBX|||4^MCH||30.30|^pg|26.40-33.20||||F<CR><LF>OBX|||5^MCHC||33.3|^g/dL|31.8-36.7||||F<CR><LF>OBX|||6^RDWsd||44.2|^fL|0.0-0.0||||F<CR><LF>OBX|||7^RDWcv||15.5|^%|13.1-24.6||||F<CR><LF>OBX|||8^PLT||324|^10³/μL|172-440||||F<CR><LF>OBX|||9^MPV||6.5|^fL|6.1-9.1||||F<CR><LF>OBX|||10^PCT||0.21|^%|0.07-0.49||||F<CR><LF>OBX|||11^PDWsd||7.3|^fL|0.0-0.0||||F<CR><LF>OBX|||12^PDWcv||45.0|^%|0.0-0.0||||F<CR><LF>OBX|||13^WBC||8.6|^10³/μL|3.7-11.7||||F<CR><LF>OBX|||14^LYM||1.8|^10³/μL|1.1-3.6||||F<CR><LF>OBX|||15^LYMP||21.0|^%|14.1-52.8||||F<CR><LF>OBX|||16^MID||0.7|^10³/μL|0.2-1.2||||F<CR><LF>OBX|||17^MIDP||8.2|^%|3.2-17.7||||F<CR><LF>OBX|||18^GRA||6.1|^10³/μL|1.9-7.9||||F<CR><LF>OBX|||19^GRAP||70.8|^%|39.6-78.4||||F<CR><LF>OBX|||20^PLCR||26|^%|0-0||||F<CR><LF>OBX|||21^<CR><LF>SFT|ACTIVE SCIENTIFIC SDN BHD|1.3.1563.0|Icon-3|1.3.1563.0|Message Version: Version_0_2_437 Software complete version: 1.3.1563.0(FE - 00 - 45)|20200811032246<CR><LF>OBR||||^^^5082^2107000223||||20210321001047|25||||3 Part Differential Hematology<CR><LF>NTE|Comment1||SITI JUMAANI|1^Name<CR><LF>NTE|Comment2||MAEPS101823|2^IC                            <CR><LF>NTE|Comment3||HALL D1|3^HALL                          <CR><LF>NTE|Comment4|||4^RBC flags<CR><LF>NTE|Comment5|||5^PLT flags<CR><LF>NTE|Comment6|||6^WBC flags<CR><LF>NTE|Comment7|||7^Technical flags<CR><LF>NTE|Comment9||91715980620_00124|9^Reagent Diluent<CR><LF>NTE|Comment10||91715980620_00124|10^Reagent System Solution<CR><LF>NTE|Comment11||91715980620_00124|11^Reagent Lyse<CR><LF>OBX|||0^RBC||4.99|^10â¶/μL|3.85-5.78||||F<CR><LF>OBX|||1^HGB||15.1|^g/dL|12.0-17.2||||F<CR><LF>OBX|||2^MCV||91.0|^fL|78.0-96.0||||F<CR><LF>OBX|||3^HCT||45.4|^%|34.8-50.9||||F<CR><LF>OBX|||4^MCH||30.30|^pg|26.40-33.20||||F<CR><LF>OBX|||5^MCHC||33.3|^g/dL|31.8-36.7||||F<CR><LF>OBX|||6^RDWsd||44.2|^fL|0.0-0.0||||F<CR><LF>OBX|||7^RDWcv||15.5|^%|13.1-24.6||||F<CR><LF>OBX|||8^PLT||324|^10³/μL|172-440||||F<CR><LF>OBX|||9^MPV||6.5|^fL|6.1-9.1||||F<CR><LF>OBX|||10^PCT||0.21|^%|0.07-0.49||||F<CR><LF>OBX|||11^PDWsd||7.3|^fL|0.0-0.0||||F<CR><LF>OBX|||12^PDWcv||45.0|^%|0.0-0.0||||F<CR><LF>OBX|||13^WBC||8.6|^10³/μL|3.7-11.7||||F<CR><LF>OBX|||14^LYM||1.8|^10³/μL|1.1-3.6||||F<CR><LF>OBX|||15^LYMP||21.0|^%|14.1-52.8||||F<CR><LF>OBX|||16^MID||0.7|^10³/μL|0.2-1.2||||F<CR><LF>OBX|||17^MIDP||8.2|^%|3.2-17.7||||F<CR><LF>OBX|||18^GRA||6.1|^10³/μL|1.9-7.9||||F<CR><LF>OBX|||19^GRAP||70.8|^%|39.6-78.4||||F<CR><LF>OBX|||20^PLCR||26|^%|0-0||||F<CR><LF>OBX|||21^','<CR><LF>'))  WITH ORDINALITY as elem(value, index),
          regexp_split_to_array(elem.value, '|') as parts
        WHERE parts[1] IN ('OBX', 'OBR') AND split_part(parts[4], '^', 2) IN ('LYM','RBC','HGB','HCT','PLT','RBC','WBC', '') AND split_part(parts[5], '^', 5) IS NOT NULL
        )s
        )s
         where code = 'OBX' 
        group by specimen_id;
 

Выходные данные похожи на это, когда они генерируют две разные записи.

Но когда я пытаюсь вставить его в таблицу, он вставляет только первую запись. Я пытаюсь зациклить процедуру, но почему-то она все еще не вставила вторую запись.

Мой полный dbfiddle здесь, скрипка