0

I have two jsonb value. I would like to merge it however I stuck to combine it together. This is my code

CREATE OR REPLACE FUNCTION emr.azintin(p_msg text)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_msgar     text[];
    v_msgln     text;
    v_currusr   afm_usr.user_id%type := 'SYSEXCH';
    v_currdt    afm_usr.insert_date%type := CURRENT_TIMESTAMP;
    macres      azt_macres%rowtype;
    orddet      lbt_orddet%rowtype;
    v_resultid  character varying ;
    v_reading   character varying ;

BEGIN   
         v_msgar := array['OBR||||^^^3972^2102001420||||20210227133222|25||||3
                            OBX|||0^RBC||4.99|^10â¶/μL|3.85-5.78||||F
                            OBX|||1^HGB||14.4|^g/dL|12.0-17.2||||F
                            OBX|||2^MCV||84.5|^fL|78.0-96.0||||F
                            OBX|||3^HCT||42.2|^%|34.8-50.9||||F
                            OBX|||4^MCH||28.90|^pg|26.40-33.20||||F
                            OBX|||5^MCHC||34.2|^g/dL|31.8-36.7|||<EOT>|F
                            OBX|||6^RDWsd||39.2|^fL|0.0-0.0||||F'];
              
            SELECT json_agg(jsonb_build_object('resultValue',split_part(parts[6],'^',1)))
               INTO v_reading
               FROM unnest(v_msgar) as elem,
                     regexp_split_to_array(elem, '\|') as parts
               WHERE parts[1] = 'OBX';
              
            SELECT json_agg(jsonb_build_object('resultId',split_part(parts[4],'^',2)))
               INTO v_resultid
               FROM unnest(v_msgar) as elem,
                     regexp_split_to_array(elem, '\|') as parts
               WHERE parts[1] = 'OBX';
              
            select concat(v_resultid || v_reading) 
               INTO macres.test_result
               FROM unnest(v_msgar) as elem,
                     regexp_split_to_array(elem, '\|') as parts
               WHERE parts[1] = 'OBX';
        
            SELECT split_part(items[5], '^', 5)
                  INTO macres.specimen_id
                FROM (
                  SELECT string_to_array(element, '|') as items
                 FROM unnest(v_msgar) as t(element)) t   
            WHERE items[1] = 'OBR';
    

    END IF;
END
$function$
;

I manage to get the v_reading and v_resultid which look like this

 [{"resultValue": "5.20"}, {"resultValue": "14.2"}, {"resultValue": "81.7"}, {"resultValue": "42.5"}, {"resultValue": "27.20"}, {"resultValue": "33.3"}, {"resultValue": "40.6"}, {"resultValue": "15.9"}, {"resultValue": "188"}, {"resultValue": "8.1"}, {"resultValue": "0.15"}, {"resultValue": "9.4"}, {"resultValue": "46.2"}, {"resultValue": "4.6"}, {"resultValue": "1.1"}, {"resultValue": "23.6"}, {"resultValue": "0.4"}, {"resultValue": "8.7"}, {"resultValue": "3.1"}, {"resultValue": "67.7"}, {"resultValue": "56"}, {"resultValue": "105"}, {"resultValue": ""}] 
[{"resultId": "RBC"}, {"resultId": "HGB"}, {"resultId": "MCV"}, {"resultId": "HCT"}, {"resultId": "MCH"}, {"resultId": "MCHC"}, {"resultId": "RDWsd"}, {"resultId": "RDWcv"}, {"resultId": "PLT"}, {"resultId": "MPV"}, {"resultId": "PCT"}, {"resultId": "PDWsd"}, {"resultId": "PDWcv"}, {"resultId": "WBC"}, {"resultId": "LYM"}, {"resultId": "LYMP"}, {"resultId": "MID"}, {"resultId": "MIDP"}, {"resultId": "GRA"}, {"resultId": "GRAP"}, {"resultId": "PLCR"}, {"resultId": "PLCC"}, {"resultId": "RBCHistogram"}]

how can i combine these two json to look like these

[{"resultid": "RBC", "resultValue": "5.20"}, {"resultid": "HGB", "resultValue": "5.20"}, {"resultid": "MCV", "resultValue": "5.20"}, {"resultid": "HCT", "resultValue": "5.20"}, {"resultid": "MCH", "resultValue": "5.20"}, {"resultid": "MCHC", "resultValue": "5.20"}, {"resultid": "RDWsd", "resultValue": "5.20"}, {"resultid": "RDWcv", "resultValue": "5.20"}, {"resultid": "PLT", "resultValue": "5.20"}, {"resultid": "MPV", "resultValue": "5.20"}, {"resultid": "PCT", "resultValue": "5.20"}, {"resultid": "PDWsd", "resultValue": "5.20"}, {"resultid": "PDWcv", "resultValue": "5.20"}, {"resultid": "WBC", "resultValue": "5.20"}, {"resultid": "LYM", "resultValue": "5.20"}, {"resultid": "LYMP", "resultValue": "5.20"}, {"resultid": "MID", "resultValue": "5.20"}, {"resultid": "MIDP", "resultValue": "5.20"}, {"resultid": "GRA", "resultValue": "5.20"}, {"resultid": "GRAP", "resultValue": "5.20"}, {"resultid": "PLCR", "resultValue": "5.20"}, {"resultid": "PLCC", "resultValue": "5.20"}, {"resultid": "RBCHistogram", "resultValue": "5.20"}]

I can simply do like these

            SELECT json_agg(jsonb_build_object('resultid',split_part(parts[4],'^',2), 'resultValue',split_part(parts[6],'^',1)))
               INTO macres.test_result
               FROM unnest(v_msgar) as elem,
                     regexp_split_to_array(elem, '\|') as parts
               WHERE parts[1] = 'OBX';

but the problem is I need to put a condition in split_part(parts[4],'^',2) thats why i seperate it into v_reading and v_resultid.

I want to put condition

IF v_resultid = 'MCHC' THEN
   v_reading = null
END ID

But somehow it still put the reading if the array found MCHC. I want to remove MCHC from my json

1
  • It's not clear what you are doing here. The JSON values do not fit your array, the array is not valid... So, two options here: Do you want us to write an entire query (which, I already did several times on your previous questions) - I would to this in one query, not in seperated... Or do you just want to know how to merge both JSON objects. Then please remove the entire confusing code to minimize your question. Commented Mar 3, 2021 at 9:50

1 Answer 1

1

Join both JSON objects:

demo:db<>fiddle

SELECT
    jsonb_agg(a.element || b.element)                        -- 4
FROM (
    SELECT * FROM jsonb_array_elements(                      -- 1
        '[{"resultValue": "5.20"}, {"resultValue": "14.2"}, {"resultValue": "81.7"}, {"resultValue": "42.5"}, {"resultValue": "27.20"}, {"resultValue": "33.3"}, {"resultValue": "40.6"}, {"resultValue": "15.9"}, {"resultValue": "188"}, {"resultValue": "8.1"}, {"resultValue": "0.15"}, {"resultValue": "9.4"}, {"resultValue": "46.2"}, {"resultValue": "4.6"}, {"resultValue": "1.1"}, {"resultValue": "23.6"}, {"resultValue": "0.4"}, {"resultValue": "8.7"}, {"resultValue": "3.1"}, {"resultValue": "67.7"}, {"resultValue": "56"}, {"resultValue": "105"}, {"resultValue": ""}]'::jsonb
    ) WITH ORDINALITY as a(element, index)                   -- 2
) a
JOIN (
    SELECT * FROM jsonb_array_elements(
        '[{"resultId": "RBC"}, {"resultId": "HGB"}, {"resultId": "MCV"}, {"resultId": "HCT"}, {"resultId": "MCH"}, {"resultId": "MCHC"}, {"resultId": "RDWsd"}, {"resultId": "RDWcv"}, {"resultId": "PLT"}, {"resultId": "MPV"}, {"resultId": "PCT"}, {"resultId": "PDWsd"}, {"resultId": "PDWcv"}, {"resultId": "WBC"}, {"resultId": "LYM"}, {"resultId": "LYMP"}, {"resultId": "MID"}, {"resultId": "MIDP"}, {"resultId": "GRA"}, {"resultId": "GRAP"}, {"resultId": "PLCR"}, {"resultId": "PLCC"}, {"resultId": "RBCHistogram"}]'::jsonb
    ) WITH ORDINALITY as b(element, index)
) b
ON a.index = b.index                                         -- 3
  1. Extract the JSON arrays into one row per element.
  2. The WITH ORDINALITY adds a row count to each expanded record to identify the original position in the array
  3. Join both extracted JSON element sets on the index
  4. Merge both JSON columns using the || operator (works only with type jsonb noth with type json, which should be used nonetheless). Finally you can aggregate these merged objects into a new JSON array.
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.