Looping in array to combine jsonb

Question

I'm unable to loop multiple array and construct it into jsonb.

CREATE TABLE emr.azt_macres (
    id serial NOT NULL,
    analyzer_test_full_desc varchar(50) NULL,
    specimen_id varchar(100) NULL,
    data_reading varchar(20) NULL,
    data_result varchar(20) NULL,
    result_status varchar(20) NULL,
    analyzer_message text NULL,
    test_result jsonb NULL,
    CONSTRAINT azt_macres_pkey PRIMARY KEY (id)
);

The array look like this. Bare in mind there are two Sample in array which is Sample123 and Sample456

1H|*^&|||Mindry^^|||||||PR|1394-97|20210225142532
P|3||QC1||^^||^^|U||||||||||||||||||||||||||
O|3|1^Sample123^1||TBILV^Bilirubin Total (VOX Method)^^
R|23|KA^Bilirubin Total (VOX Method)^^F|17.648074^^^^|µmol/L|
R|24|ATU^Alanine Aminotransferase^^F|58.934098^^^^|U/L|
L|1|N
1H|*^&|||Mindry^^|||||||PR|1394-97|20210225142532
P|3||QC1||^^||^^|U||||||||||||||||||||||||||
O|3|1^Sample456^1||TBILV^Bilirubin Total (VOX Method)
R|23|TBILV^Bilirubin Total (VOX Method)^^F|17.648074^^^^|
R|24|ALT^Alanine Aminotransferase^^F|58.934098^^^^|U/L|
R|25|TTU^Alkaline phosphatase^^F|92.675340^^^^|U/L|^|N||

I'm able to insert if there are only 1 sample barcode, however the second sample_id is not insert. Do I need to loop the array again?

my code:

CREATE OR REPLACE FUNCTION emr.azintin(v_msgar)
 RETURNS jsonb
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_cnt       INT = 0;
    v_msgar     text[];
    v_msgln     text;
    v_msgtyp    character varying(3);
    v_tmp       text;
    macres      azt_macres%rowtype;

BEGIN   

            macres.analyzer_test_full_desc := 'CHEMO';
        
            SELECT  split_part(items[3], '^', 2)
                  INTO macres.specimen_id
                FROM (
                  SELECT string_to_array(element, '|') as items
                 FROM unnest(v_msgar) as t(element)) t   
            WHERE items[1] = 'O';
        

            SELECT jsonb_agg(jsonb_build_object('resultId', split_part(items[3],'^',1),'resultValue',split_part(items[4],'^',1)))
               INTO macres.test_result
               FROM (
                  SELECT string_to_array(element, '|') as items
                 FROM unnest(v_msgar) as t(element)) t   
            WHERE items[1] = 'R';
   
        
              v_cnt := v_cnt + 1;

        
                    BEGIN
                        INSERT INTO azt_macres(analyzer_test_full_desc, specimen_id, data_reading ,
                            data_result,result_status,analyzer_message,test_result)
                            VALUES (macres.analyzer_test_full_desc, macres.specimen_id,macres.data_reading,
                                macres.data_result,macres.result_status,macres.analyzer_message, macres.test_result);
                    END;
END
$function$
;

Currently my output is like this

specimen_id	test_result
sample123	[{"resultId": "KA", "resultValue": "17.648074"}, {"resultId": "ATU", "resultValue":"58.934098"}, {"resultId": "TBILV", "resultValue": "17.648074"}, {"resultId": "ALT","resultValue": "58.934098"}, {"resultId": "TTU", "resultValue": "92.675340"}]

supposely my output should be like this

specimen_id	test_result
sample123	[{"resultId": "KA", "resultValue": "17.648074"}, {"resultId": "ATU","resultValue":"58.934098"}]
sample456	[{"resultId": "TBILV", "resultValue": "17.648074"}, {"resultId": "ALT","resultValue": "58.934098"}, {"resultId": "TTU", "resultValue": "92.675340"}]

Answer 1

1. Creating the output:

As I see it, you need a group of the R elements by the previous O elements. That needs to create a group of connected R elements, which share the same O value:

step-by-step demo:db<>fiddle

Don't be afraid, it looks heavier than it is ;) To understand all described intermediate steps, please have a look at the fiddle where every step is executed separately to demonstrate its impact.

SELECT
    specimen_id,
    json_agg(result) as test_result                                             -- 9
FROM (
    SELECT
        code,
        first_value(                                                            -- 5
            split_part(id, '^', 2)
        ) OVER (PARTITION BY group_id ORDER BY index) as specimen_id,
        json_build_object(                                                      -- 7
            'result_id',
            split_part(id, '^', 1),                                             -- 6
            'result_value',
            split_part(value, '^', 1)
        ) as result
    FROM (
        SELECT
            parts[1] as code,
            parts[3] as id,
            parts[4] as value,
            elem.index,
            SUM((parts[1] = 'O')::int) OVER (ORDER BY elem.index) as group_id   -- 4
        FROM mytable t,
            unnest(myarray) WITH ORDINALITY as elem(value, index),              -- 1
            regexp_split_to_array(elem.value, '\|') as parts                    -- 2
       WHERE parts[1] IN ('R', 'O')                                             -- 3
    ) s
) s
WHERE code = 'R'                                                                -- 8
GROUP BY specimen_id

1. Put all array elements into separate records. WITH ORDINALITY adds an index to the records which represents the original position of the element in the original array.
2. Split the array elements into their parts (delimited by | character)
3. Filter only the R and O elements.
4. This is the most interesting part: Creating the group of connected records. The idea: Every O records gets a value 1 (boolean true cast into int is 1, so: (parts[1] = 'O')::int), every R gets a 0 value. The cumulative SUM() window function creates a total sum of the current and every previous record. So every O increases the sum value, the R values (add 0) keep this value. So this generates the same group identifier for every O record and all directly following R records. To ensure the correct record order, we use the index, which was created by the WITH ORDINALITY in step 1.
5. The first_value() window function gives the first value of a group (= partition), in this case, it adds to every record the first value of the recently created group. This finally associates the O value with each related R record.
6. split_part() for retrieving the correct values from the strings
7. build your JSON object for every record
8. Remove the O records
9. Group by the specimen_id (which was added in step 5) and aggregate the result values into an array

Now we have the expected output

2. Inserting:

Insert is easy: You can take the result (maybe you need some columns more, just add them to the SELECT statement, we recently created and execute the INSERT statement:

INSERT INTO mytable (col1, col2) 
SELECT -- <query from above>

3. Create function:

Here it is not clear to me, what you want to achieve. Do you want to give the array as input parameter, or do you want to query a table? Why are you using a JSON return type although nothing is returned, ...

However, of course, you can put all the stuff into a stored procedure. Here I assume you give the array as input parameter into the procedure (for example, see the answer to your previous question):

CREATE FUNCTION my_function(myarray text[]) RETURNS void AS $$
BEGIN
    INSERT INTO ...
    SELECT ...
END;
$$ LANGUAGE plpgsql;