Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

iterate over json array elements in postgresql

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.

like image 364
lapots Avatar asked Sep 05 '26 06:09

lapots


1 Answers

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

like image 86
Vao Tsun Avatar answered Sep 06 '26 18:09

Vao Tsun



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!