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.

Vao Tsun · Accepted Answer · 2018-05-16 10:19:36Z

9

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

answered May 16, 2018 at 10:19

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

3 Comments

lapots Over a year ago

what to do if each entry has its own subarray in a field like { "name" : "value", "fields": ["a1", "a2"] }?

Vao Tsun Over a year ago

write a recursive CTE I'd say

user9645 Over a year ago

Thanks - I know how to do it with FOR r IN (...) but I was hoping to avoid declaring a RECORD variable.

Evgeny Nozdrev · Accepted Answer · 2021-07-23 11:50:49Z

3

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)

edited Jul 23, 2021 at 11:50

answered May 16, 2018 at 10:22

Evgeny Nozdrev

1,56613 silver badges15 bronze badges

5 Comments

lapots Over a year ago

so basically it returns a table with a single column? can I convert it into array?

Evgeny Nozdrev Over a year ago

Yes. Edited the answer.

user9645 Over a year ago

The second example does not work: getting ERROR: 42601: syntax error at or near "SELECT" - using version 11. Also tried putting the sub-query in parenthesis but same result.

Evgeny Nozdrev Over a year ago

@user9645 please check if you did not forget LOOP ... END LOOP;. Note that I do not have Postgres 11 to test the answer.

user9645 Over a year ago

I did not forget the loop/end loop - I just pasted your FOREACH element in ARRAY SELECT... statement into my test case and get 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 I suggest you verify that you can use ARRAY against a SELECT statement like that.

Govind · Accepted Answer · 2023-02-08 05:20:40Z

0

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.

edited Feb 8, 2023 at 5:20

answered Feb 8, 2023 at 5:14

Govind

335 bronze badges

Collectives™ on Stack Overflow

iterate over json array elements in postgresql

3 Answers 3

3 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related