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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With