Question 89737 in PostgreSQL Question posted in
The official documentation can be found here.

how to iterate and append jsonb in postgres db function – Postgresql

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$;
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    The added empty object noise can be avoided like instructed by Lukasz.

    While being at it, start over. Almost everything else is incorrect or inefficient, too:

    CREATE OR REPLACE FUNCTION my_wrapper_function()
  RETURNS jsonb
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT COALESCE(jsonb_agg(my_function(column1)::jsonb), '[]'::jsonb)
FROM  (SELECT DISTINCT column1 FROM table1) sub;
$func$;

    That’s assuming my_function() is actually STABLE.

    Or just use the bare SQL statement, there is little utility in the remaining my_wrapper_function() now.
    You may not even need COALESCE, nor the added cast:

    SELECT jsonb_agg(my_function(column1))
FROM  (SELECT DISTINCT column1 FROM table1) sub;

    A query with an aggregate function and no GROUP BY always returns exactly one row.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search