在PostgreSQL中迭代JSON数组元素

Question

我有一个长这样的json数据：

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

我想迭代它的元素并打印它们。我是这样做的：

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;
$$;

但是出现以下错误：查询select jsonb_array_elements(datajson->'elements')返回了多行。

问题出在哪里呢？

更新

尝试了这个建议：

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;
$$;

但是这样只会出现语法错误：

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

尝试在不使用FOR r IN (...)和声明RECORD变量的情况下完成此操作。

- lapots

Answer 1

只是：

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)

但如果您想提高它：

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

我猜您把json数组和postgres数组搞混了——它们是不一样的。而jsonb_array_elements返回的是一个集合（setof），而不是一个数组

- Vao Tsun

---

如果每个条目都在字段中有自己的子数组，例如 { "name" : "value", "fields": ["a1", "a2"] }，该怎么办？ - lapots

编写递归CTE，我会说。 - Vao Tsun

谢谢 - 我知道可以使用 FOR r IN (...) 来实现，但我希望避免声明一个 RECORD 变量。 - user9645

Answer 2

jsonb_array_elements()函数返回数组的元素作为表，而不是数组。

使用

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

遍历表格行而不是数组。

或使用 array_agg() 将表格转换为数组：

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

但是它可能运行得更慢（表格将像之前的情况一样被创建，然后被转换）

- Evgeny Nozdrev

---

所以基本上它返回一个只有一列的表格？我能把它转换成数组吗？ - lapots

是的。已编辑答案。 - Evgeny Nozdrev

第二个示例不起作用：出现ERROR: 42601：语法错误或附近的“SELECT” - 使用版本11。也尝试将子查询放在括号中，但结果相同。 - user9645

@user9645，请检查您是否忘记了 LOOP ... END LOOP;。请注意，我没有Postgres 11来测试答案。 - Evgeny Nozdrev

我没有忘记循环/结束循环 - 我只是把你的 FOREACH element in ARRAY SELECT... 语句粘贴到我的测试用例中，然后出现了以下错误信息：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。我建议您验证一下是否可以对像那样使用 ARRAY 与 SELECT 语句。 - user9645

Answer 3

json_array_elements_text ( json ) → 返回文本集合

jsonb_array_elements_text ( jsonb ) → 返回文本集合

对于元素，使用以下语句： SELECT jsonb_array_elements_text FROM jsonb_array_elements_text(datajson->'elements') LOOP ... END LOOP;

必须将“element”声明为TEXT。 现在我们可以在循环内部将element用作TEXT。

- Govind