Postgres中json_build_array()内使用FOR循环出现语法错误

你遇到的语法错误是因为不能在普通SQL语句的json_build_array()中直接使用PL/pgSQL的for循环语法——for循环属于过程式的PL/pgSQL语法，只能在存储函数/过程内部使用，无法嵌入到纯SQL的函数调用中。

要生成包含多个指定结构元素的JSON数组，有两种适配场景的解决方案：

方案1：纯SQL语句实现（适合你的当前代码场景） #

使用jsonb_agg()聚合函数配合子查询，替代循环生成数组元素：

select jsonb_build_object(
    'start', jsonb_build_object(
        'inv', (
            -- 子查询中生成每个JSON对象，再用jsonb_agg聚合成数组
            select jsonb_agg(jsonb_build_object('key1', 'value1', 'id', i))
            from generate_series(1,5) as t(i)
        )
    )
);

这里generate_series(1,5)生成1到5的序列，每个i对应一个jsonb_build_object生成的对象，最后通过jsonb_agg将所有对象聚合为一个JSON数组。

方案2：PL/pgSQL函数内实现（如果是在存储函数中开发） #

如果你的代码是写在PL/pgSQL存储函数里，可以先通过循环构建数组变量，再嵌入到最终的JSON结构中：

create or replace function generate_custom_json() returns jsonb as $$
declare
    inv_elements jsonb[];
    i integer;
begin
    -- 循环生成每个JSON对象，存入数组变量
    for i in 1..5 loop
        inv_elements := inv_elements || jsonb_build_object('key1', 'value1', 'id', i);
    end loop;

    -- 组装最终的JSON结构
    return jsonb_build_object(
        'start', jsonb_build_object(
            'inv', to_jsonb(inv_elements)
        )
    );
end;
$$ language plpgsql;

调用函数即可得到结果：

select generate_custom_json();

内容的提问来源于stack exchange，提问作者user2529660