向PostgreSQL函数传递含对象数组的JSON时出现数组字面量格式错误

这个错误ERROR: malformed array literal: "{"id": 1}"几乎可以肯定是你在函数内部错误地把JSON数组当成了PostgreSQL的原生数组来处理——比如试图用强制类型转换（::text[]）把JSON数组转成PostgreSQL的文本数组，但JSON数组的元素是JSON对象，这种转换是不兼容的。

错误根源示例 #

假设你在函数第8行写了类似这样的代码：

-- 错误写法：试图将JSON数组直接转为PostgreSQL原生数组
SELECT unnest((sync_data->>'shops')::text[]) INTO some_variable;

这里sync_data->>'shops'返回的是字符串形式的JSON数组"[{"id": 1}, {"id": 2}]"，PostgreSQL在尝试把它转成text[]时，会把{"id": 1}当成单个数组元素，但元素里的大括号和引号会破坏PostgreSQL原生数组的格式规则，从而抛出"malformed array literal"错误。

正确的处理方式 #

你需要使用PostgreSQL专门提供的JSON处理函数来操作JSON数组，而不是强制转换为原生数组。下面是修复后的函数示例：

CREATE OR REPLACE FUNCTION foo(sync_data json) RETURNS json AS $body$
DECLARE
    shop_obj json;
    processed_result jsonb := '[]'::jsonb;
BEGIN
    -- 使用json_array_elements遍历JSON数组中的每个对象
    FOR shop_obj IN SELECT json_array_elements(sync_data->'shops') LOOP
        -- 示例：提取每个店铺的id并处理
        processed_result := processed_result || jsonb_build_object(
            'shop_id', (shop_obj->>'id')::int,
            'device_id', (sync_data->>'deviceId')::int
        );
    END LOOP;
    
    -- 返回处理后的结果（这里转成json类型）
    RETURN processed_result::json;
END;
$body$ LANGUAGE plpgsql;

调用方式不变 #

你原来的调用语句完全没问题，直接执行即可：

SELECT foo('{"deviceId": 1, "shops": [{"id": 1}, {"id": 2}]}'::json);

额外建议 #

如果你的PostgreSQL版本是9.5及以上，推荐使用jsonb类型替代json——它支持更高效的索引和操作，处理复杂JSON时性能更好。只需要把函数参数和返回值改成jsonb，调用时用::jsonb转换，遍历函数换成jsonb_array_elements即可。

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