PostgreSQL jsonb参数函数无法提取vendorid等字段值问题排查

问题背景 #

创建了接收JSONB参数的insertorupdatevendorcontactnos函数，目标是提取JSONB中的值存入vendorcontactnos和vendorcontactnoshistory表，但调用后vendorcontactnos表的vendorid、createdby等字段未正确写入预期值。

错误原因 #

调用函数时传入的是JSON数组（外层包含[]），但原函数将参数contactnos直接视为单个JSON对象，尝试用contactnos->>'vendorid'提取顶层键值。由于数组本身没有vendorid、createdby这些键，提取结果为NULL，导致插入的字段值为空。

解决方案 #

提供两种修正方案，根据实际业务需求选择：

方案1：修改函数支持传入JSON数组 #

如果需要批量处理多个vendor数据，修改函数遍历外层数组中的每个对象：

CREATE OR REPLACE FUNCTION public.insertorupdatevendorcontactnos(
    contactnos jsonb)
    RETURNS TABLE(vendorhistory_id bigint, vendor_id bigint) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    rec jsonb;
BEGIN
    -- 遍历外层数组中的每个vendor对象
    FOR rec IN SELECT * FROM jsonb_array_elements(contactnos) LOOP
        -- 写入vendorcontactnos表
        INSERT INTO vendorcontactnos (vendorid, key, value, createdby, createdon)
        SELECT 
            (rec->>'vendorid')::bigint,
            (x->>'key')::integer,
            x->>'value',
            (rec->>'createdby')::integer,
            NOW()
        FROM jsonb_array_elements(rec->'items') AS x;
        
        -- 写入vendorcontactnoshistory表
        INSERT INTO public.vendorcontactnoshistory(vendorid, vendorhistoryid, key, value, createdby, createdon)
        SELECT 
            (rec->>'vendorid')::bigint,
            (rec->>'vendorhistoryid')::bigint,
            (x->>'key')::integer,
            x->>'value',
            (rec->>'createdby')::integer,
            NOW()
        FROM jsonb_array_elements(rec->'items') AS x;
        
        -- 返回当前vendor的关联ID
        RETURN QUERY SELECT (rec->>'vendorid')::bigint, (rec->>'vendorhistoryid')::bigint;
    END LOOP;
END;
$BODY$;

方案2：修改调用语句传入单个JSON对象 #

如果每次仅处理单个vendor数据，去掉调用语句中外层的[]：

SELECT * FROM insertorupdatevendorcontactnos('{"vendorid":100,
    "vendorhistoryid":1,
    "createdby":5,
    "items":[ 
      {"key":1, "value":"+19876543210"},
      {"key":2, "value":"+16543219870"},
      {"key":3, "value":"+13210654987"}
    ]}');

验证结果 #

执行上述任一修正方案后，vendorcontactnos表将正确写入预期数据：

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