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

@FrankHeikens I have not included the full SQL. I have only included the part where I need some help. This is part of a function that I am working on. — user2529660
– user2529660, Commented Nov 8, 2023 at 18:33
Post the entire code, not half of it. And the result you're looking for. A loop is something I always try to avoid, in most cases you don't need them — Frank Heikens
– Frank Heikens, Commented Nov 8, 2023 at 18:42
@FrankHeikens I have added the expected output and have added what I have worked on until now. — user2529660
– user2529660, Commented Nov 8, 2023 at 20:18

Erwin Brandstetter · Accepted Answer · 2023-11-08 19:01:08Z

2

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

Postgres FOR LOOP

edited Nov 8, 2023 at 19:01

answered Nov 8, 2023 at 18:19

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2529660 Over a year ago

Your solution works, but I have a question. I checked the values for all the elements inside both the arrays and both arrays have the same value. Is it possible to have different values for both arrays?

Erwin Brandstetter Over a year ago

@user2529660: There is only one array. (?) But anything is possible.

user2529660 Over a year ago

Yes, you're right. There is only 1 array, but there are 2 objects inside that 1 array and I want to know if it is possible to have different values for same variables inside the 2 objects.

Erwin Brandstetter Over a year ago

@user2529660 Anything is possible. I suggest you start a new question with the specifics. You can always link to this question for context, but don't change this one after it has been answered.

Collectives™ on Stack Overflow

FOR loop inside json_build_array() function does not work

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related