CODEWITHSUNDEEP
sqljsonpostgresqlplpgsql
lapots
lapots

Reputation: 13405

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.

Upvotes: 6

Views: 25985

Answers (3)

Govind
Govind

Reputation: 33

json_array_elements_text ( json ) → setof text

jsonb_array_elements_text ( jsonb ) → setof text

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

'element' has to be declared as a TEXT. Now we can use element as a TEXT inside the LOOP.

Upvotes: 0

Evgeny Nozdrev
Evgeny Nozdrev

Reputation: 1566

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)

Upvotes: 3

Vao Tsun
Vao Tsun

Reputation: 51609

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

Upvotes: 9

Related Questions