How to object in row_to_json and array_agg from multiple value

Question

I manage to create a jsonb output, however I need to make it as object

My current code

create function extract_data()
  returns jsonb
as
$$
declare
  v_msgar text[];
  v_result jsonb;
  analyzer_test_full_desc character varying;
  data_reading character varying;
begin
  v_msgar := array['P|61||||^^||^^|U||||||||||||||||||||||||||<CR>',
                   'O|61|15^1^15|KK4002259|GLU^Glucose (GOD-POD Method)^^',
                   'R|170|AST^Aspartate Aminotransferase^^F|22.657989^^^^',
                   'R|171|ALP^Alkaline phosphatase^^F|107.636995^^^^',
                   'R|172|TP^Total Protein^^F|85.245151^^^^',
                   'R|173|TG^Triglycerides^^F|1.348633^^^^',
                   'R|174|HDL^HDL-Cholesterol^^F|1.238458^^^^',
                   'R|175|CHOL^Total Cholesterol^^F|5.073630^^^^',
                   'R|176|UA^Uric Acid^^F|309.705876^^^^',
                   'R|177|BUN^Urea^^F|4.412234^^^^'];

           SELECT split_part(items[3], '^', 1)
                  INTO  analyzer_test_full_desc
                FROM (
                  SELECT string_to_array(element, '|') as items
                 FROM unnest(v_msgar) as t(element)) t   
            WHERE items[1] = 'R';
        
            SELECT  split_part(items[4], '^', 1)
                  INTO data_reading
                FROM (
                  SELECT string_to_array(element, '|') as items
                 FROM unnest(v_msgar) as t(element)) t   
            WHERE items[1] = 'R';

            SELECT array_to_json(array_agg(row_to_json(t))) 
                 FROM (
                SELECT analyzer_test_full_desc as resultId, data_reading as resultValue
            ) t INTO v_result;
  
  return v_result;
end;  
$$
language plpgsql;

The output is

[{"resultid": "AST", "resultvalue": "22.657989"}]

I expected the out put is something like this

[{"resultid": "AST", "resultvalue": "22.657989"},{"resultid": "ALP", "resultvalue": "107.636995"},{"resultid": "TP", "resultvalue": "85.245151"]

I try to loop the select statement however still didnt have the solution.

Stefanov.sm · Accepted Answer · 2021-02-25 07:56:04Z

You do not need pl/pgsql to do this. Extract the data parts that you need in one (inner) query and then aggregate them like this:

create or replace function extract_data(text[]) returns jsonb as
$$
select jsonb_agg(to_jsonb(t.*)) from 
(
 select split_part(split_part(arr, '|', 3), '^', 1) as "resultId", 
        split_part(split_part(arr, '|', 4), '^', 1) as "resultValue"
 from unnest($1) arr
 where split_part(arr, '|', 1) = 'R'
) t;
$$ language sql;

select extract_data(array[
 'P|61||||^^||^^|U||||||||||||||||||||||||||<CR>',
 'O|61|15^1^15|KK4002259|GLU^Glucose (GOD-POD Method)^^',
 'R|170|AST^Aspartate Aminotransferase^^F|22.657989^^^^',
 'R|171|ALP^Alkaline phosphatase^^F|107.636995^^^^',
 'R|172|TP^Total Protein^^F|85.245151^^^^',
 'R|173|TG^Triglycerides^^F|1.348633^^^^',
 'R|174|HDL^HDL-Cholesterol^^F|1.238458^^^^',
 'R|175|CHOL^Total Cholesterol^^F|5.073630^^^^',
 'R|176|UA^Uric Acid^^F|309.705876^^^^',
 'R|177|BUN^Urea^^F|4.412234^^^^'
]);

In order to make the function reusable pass the data array as an argument rather than hard-code it inside the function body.

marc_s · Accepted Answer · 2021-03-11 06:03:27Z

step-by-step demo:db<>fiddle

You don't necessarily need a stored procedure, a simple query is enough:

SELECT
    jsonb_agg(                                      -- 6
        jsonb_build_object(                         -- 5
            'resultid',
            split_part(parts[3],'^',1),             -- 4
            'resultvalue',
            split_part(parts[4],'^',1)
        )
    )
FROM mytable t,
    unnest(myarray) as elem,                        -- 1
    regexp_split_to_array(elem, '\|') as parts      -- 2
WHERE parts[1] = 'R'                                -- 3

Expand all array elements into one record each
Split the elements into a new array on delimiter |
Filter all R elements
Take the expected parts and retrieve the value before the ^ character
Build the expected JSON elements
Aggregate them into a JSON array

However, if you need a stored procedure, you can wrap this query into one, of course:

create function extract_data()
  returns jsonb
as
$$
declare
  v_result jsonb;
begin

    SELECT
        ...
    INTO v_result
    FROM ...

    RETURN v_result;

end;  
$$
language plpgsql;

Finally, you can put the array as function input parameter as well instead of taking a table input:

create function extract_data(myarray text[])
  returns jsonb
as
$$
declare
  v_result jsonb;
begin

    SELECT
        jsonb_agg(
            jsonb_build_object(
                'resultid',
                split_part(parts[3],'^',1),
                'resultvalue',
                split_part(parts[4],'^',1)
            )
        )
    INTO v_result
    FROM unnest(myarray) as elem,
        regexp_split_to_array(elem, '\|') as parts
    WHERE parts[1] = 'R';

    RETURN v_result;

end;  
$$
language plpgsql;

Collectives™ on Stack Overflow

How to object in row_to_json and array_agg from multiple value

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related