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

Postgresql – FOR loop inside json_build_array() function does not work

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".

Tags:

2

Answers


  1. 0

    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:

    Related:

    Login or Signup to reply.
  2. 0

    Still not clear to me where the data should come from, but if you have a table "invoices" and that table has the columns fname and lname, you could create the json-output using this piece of SQL:

    SELECT JSONB_BUILD_OBJECT(
               'start', JSONB_BUILD_OBJECT(
                'inv', JSONB_AGG(
                        JSONB_BUILD_OBJECT(
                                'fname', fname
                            ,   'lname', lname)
                       )
                        ))
FROM invoices;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search