Generate JSON object from a dynamic set of input functions

Question

I am trying to write a function (f_all) that basically just calls other "partial" functions (e.g. f_1,f_2,f_3) and merges the results of them into one final JSON object. Function f_all will take a string array (e.g '1,2,3') and for every number in the array, it will add corresponding key in the resulting JSON , e.g. "part1" and value, which will be a result of the "partial" function. So for example, for f_all('1,2,3') it should return JSON:

{"part1": {<result of f_1>},
 "part2": {<result of f_2>},
 "part3": {<result of f_3>}
}

I managed to achieve something similar using a FOR loop to append the result of each "partial" function to resulting array with array_append(). However, I need the result to be a simple JSON/JSONB object, not an array of objects.

This works and returns an array of json objects:

CREATE OR REPLACE FUNCTION f_all(ingeom geometry, parts text)
 RETURNS jsonb []
 LANGUAGE plpgsql
AS $function$
DECLARE 
    arr TEXT ARRAY;
    i text;
    json_partx jsonb;
    resultjson_part jsonb;
    resultjson jsonb;
    resultarray json [];
 BEGIN
    arr = string_to_array(parts, ',');
    
    FOREACH i in ARRAY arr
    LOOP
        EXECUTE 'SELECT f_' || i || '($1)' into json_partx using ingeom;
        resultjson_part = json_build_object('part' || i, json_partx);
        resultjson =  array_append(resultjson, resultjson_part);
    END LOOP;
        
    RETURN (resultjson);
END;
$function$;

In a similar manner, I want, in each loop iteration, to append key and value pairs into JSON object and return the resulting object.

Erwin Brandstetter · Answer 1 · 2022-05-05T02:04:46.593

You write:

Function f_all will take a string array (e.g '1,2,3')

But that's not an array. It's a list of values passed as plain text. Pass an actual array instead. Better yet, use a VARIADIC function so you can pass an array or a list of values.

Your function is open to SQL injection, which is a big no-go.

Also worth mentioning that the data type geometry used here requires the additional module PostGis installed.

Assuming all your functions f_1() ... f_N() return a scalar result (not a set!), consider this radically simpler, faster and safer function:

CREATE OR REPLACE FUNCTION f_all(_ingeom geometry, VARIADIC _parts int[], OUT _result jsonb)
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF _parts <> '{}' THEN  -- rule out NULL and empty array as input
      EXECUTE 'SELECT to_jsonb(t.*) FROM (SELECT f_' || array_to_string(_parts, '($1), f_')
                                                     || '($1)) AS t(part'
                                                     || array_to_string(_parts, ', part') || ')'
      INTO  _result
      USING _ingeom;
   ELSE
      RAISE EXCEPTION 'Parameter "_parts" must not be empty or NULL!';
   END IF;
END
$func$;

Returns a JSON object (as jsonb) as requested.

Call:

SELECT f_all('foo', 1, 2, 3);

Or:

SELECT f_all('foo', VARIADIC '{1,2,3}');

The function takes integer input, which rules out SQL injection a priori.

Using a subselect to conveniently build the JSON object. Alternatively, you could use jsonb_object() or jsonb_build_object(). See:

Return multiple columns of the same row as JSON array of objects

Using a VARIADIC function for convenience. See:

Using an OUT parameter for convenience. See:

To address your original (now void) question: use the concatenation operator || to "append" JSON objects:

SELECT jsonb '{"foo":1}' || jsonb '{"bar":"baz"}';

Generate JSON object from a dynamic set of input functions

1 Answers1