FOR loop inside json_build_array() function does not work

Question

I am currently working on a function in Postgres. I have the function working, but I need to add a loop inside the json_build_array() function to add multiple of what is inside the array. This is what I currently have:

select jsonb_build_object('start', jsonb_build_object(
        'inv', json_build_array(
            for i in select * from generate_series(1,5) loop
            jsonb_build_object(...
            end loop

But I get an error saying:

Syntax error at or near "for".

Answer 1

The FOR loop is a PL/pgSQL syntax element that is not allowed within an SQL statement (even if that's nested in a PL/pgSQL block).

Your obvious intent can be implemented like this:

SELECT jsonb_build_object('start', jsonb_build_object(
          'inv', ((SELECT jsonb_agg(some_name)
                   FROM   jsonb_build_object('foo', 1) AS some_name, generate_series(1,5)))));

fiddle

Replace jsonb_build_object('foo', 1) AS some_name with the actual object and name you need.

Of course, you have to assign or return the result in a PL/pgSQL block. See:

• SELECT or PERFORM in a PL/pgSQL function

Related:

• Postgres FOR LOOP