PostgreSQL函数内追加JSON数组时输出格式异常问题求助

问题根因 #

你使用的array_append是PostgreSQL用于操作原生数组类型（如text[]、int[]）的函数，并不适用于JSON类型的数组。用它追加JSON值时，系统会自动将JSON内容转义为字符串后塞入原生数组，最终输出的就是你遇到的带多余引号、不符合JSON规范的格式。

修复方案 #

步骤1：确认变量类型 #

存储结果的my_json_result_array变量需要声明为JSON（推荐性能更好的JSONB类型），初始值设置为'[]'::json（对应JSONB则为'[]'::jsonb），不要使用原生数组类型。

步骤2：替换追加逻辑 #

把原来的array_append调用替换为JSON数组专用的追加操作：

• 如果你用的是JSON类型：

-- 替换原来的 select array_append(...) into ... 行
select json_array_append(my_json_result_array, cat_json) into my_json_result_array;

• 如果你用的是JSONB类型（推荐），可以直接用更简洁的操作符：

my_json_result_array := my_json_result_array || cat_json;

修改后返回的my_json_result_array就是标准的JSON数组格式，可以直接在代码中解码。

性能优化建议 #

你现在写的嵌套循环逻辑性能较差，不需要逐行循环判断，可以直接用单条SQL生成符合要求的结果，代码更简洁性能也更高：

SELECT json_agg(row_to_json(c.*)) AS my_json_result_array
FROM categories c
WHERE c.is_active = '1'
AND EXISTS (
    SELECT 1 
    FROM public."V_category_to_sub_category_w_names" cs
    JOIN public."V_APP_products_w_sub_categories" p 
        ON cs.sub_category_id = p.sub_category_id
    WHERE cs.category_id = c.category_id 
        AND cs.sub_category_is_active = '1'
        AND p.store_id = get_store_id 
        AND p.is_deleted = '0'
);

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