iterate over json array elements in postgresql

Question

I've got a json that looks like this

{
    "elements": [ "element1", "element2", "element3" ]
}

I want to iterate over its element and print them. I do it like this

do $$
declare
    datajson jsonb := '{
        "elements": [ "element1", "element2", "element3", "element4" ]
    }';
    element varchar(128);
begin
    foreach element in array jsonb_array_elements(datajson->'elements')
    loop
        raise notice '%', element;
    end loop;
end;
$$;

But it fails with the error query select jsonb_array_elements(datajson->'elements') returned more than one row.

What is the problem?

UPDATE

Tried this suggestion:

do $$
declare
    datajson jsonb := '{
        "elements": [ "element1", "element2", "element3", "element4" ]
    }';
    element varchar(128);
begin
    foreach element in array
        SELECT array_agg(jsonb_array_elements) FROM jsonb_array_elements(datajson->'elements')
    loop
        raise notice '%', element;
    end loop;
end;
$$;

But that just gives a syntax error:

ERROR:  42601: syntax error at or near "SELECT"
LINE 7:     SELECT array_agg(jsonb_array_elements) FROM jsonb_array_...
            ^
LOCATION:  scanner_yyerror, scan.l:1134

Trying to do this without using FOR r IN (...) and declaring a RECORD variable.

Solution 1

just:

so=# with c(j) as (values('{
    "elements": [ "element1", "element2", "element3" ]
}'::jsonb))
select jsonb_array_elements(j->'elements') from c;
 jsonb_array_elements
----------------------
 "element1"
 "element2"
 "element3"
(3 rows)

but if you want to raise it:

so=# do $$
declare
    datajson jsonb := '{
        "elements": [ "element1", "element2", "element3", "element4" ]
    }';
    element varchar(128);
    r record;
begin
    for r in (select jsonb_array_elements(datajson->'elements') element)
    loop
        raise notice '%', r.element;
    end loop;
end;
$$;
NOTICE:  "element1"
NOTICE:  "element2"
NOTICE:  "element3"
NOTICE:  "element4"
DO

I assume you confuse json array with postgres array - they are not the same. And jsonb_array_elements returns setof, not the array

Solution 2

jsonb_array_elements() returns elements of array as table, not as array.

Use

FOR element IN
    SELECT jsonb_array_elements FROM jsonb_array_elements(datajson->'elements')
LOOP
     ...
END LOOP;

to iterate over table rows, not array.

Or use array_agg() to convert a table into array:

FOREACH element IN ARRAY
    (SELECT array_agg(jsonb_array_elements) FROM jsonb_array_elements(datajson->'elements'))
LOOP
     ...
END LOOP;

but it may work slower (table will be created as in previous case, and then will be converted)