Search code examples
postgresqlaggregate-functionsplpgsqljsonb

how to iterate and append jsonb in postgres db function


I have a DB function, say "myWrapperFunction"(), which returns a JSON array of objects as below:

select * from "myWrapperFunction"()

>> [{"a":"b","c":"d"},{"e":"f"}]

I have a table "table1" with column 'column1'.

I need to pass the values from table1.column1 into myFunction() as argument and build one json array from the results.

I have created the below db function and it works fine with only one problem: it appends an empty json object at the start:

[{},{"a":"b","c":"d"},{"e":"f"},{"k":"l"}]

How to get rid of that leading empty json object?

CREATE OR REPLACE FUNCTION myWrapperFunction()
    RETURNS SETOF json 
    LANGUAGE 'plpgsql'

    COST 100
    STABLE STRICT 
    ROWS 1000
AS $BODY$
DECLARE
    _elements INTEGER[];
    _element INTEGER;
    _results json;
    _result json;
    _all_result jsonb;
    val json ='{}'::json;

BEGIN
  SELECT  ARRAY_AGG( DISTINCT column1) into _elements from table1;
  FOREACH _element IN ARRAY _elements 
  LOOP
        SELECT * FROM  myFunction(_element) into _results; 
    IF _results IS NOT null THEN
        val=val::jsonb||_results::jsonb;
    END IF;
  END LOOP;
  RETURN QUERY select val;
  RETURN  ;
END; $BODY$;

Solution

  • The initial value for val is val json ='{}'::jsonb; which is later concatenated inside the loop:

    val=val::jsonb||_results::jsonb;
    

    For sample:

    SELECT '{}'::JSONB || '[{"a":"b"}]'::JSONB;
    -- [{}, {"a": "b"}]
    --  ^^
    

    It could be replaced with val json ='[]'::jsonb;:

    SELECT '[]'::JSONB || '[{"a":"b"}]'::JSONB
    -- [{"a": "b"}]
    

    db<>fiddle demo