PostgreSQL函数报错：无法从对象提取元素（附代码示例）

问题背景 #

编写insertorupdateinvoice(jsonb)函数，用于将传入的JSONB数据拆分后插入一对多关系的invoicemaster和lineitemmaster表，但调用时触发"cannot extract elements from an object"错误。

错误原因 #

1. 主表插入的核心问题：使用jsonb_array_elements(invoice)解析传入的JSON参数，但传入的是单个JSON对象而非JSON数组，该函数仅能处理数组类型，因此触发报错。
2. 类型转换语法错误：部分字段的类型转换括号位置错误，例如j.invoice->>'invoiceno'::character应为(j.invoice->>'invoiceno')::character，否则会先对字符串字面量做类型转换，而非对提取出的字段值转换。
3. 子项引用错误：插入lineitemmaster时，对jsonb_array_elements(invoice->'lineitems')的结果引用错误，应直接使用x->>'字段名'而非x.invoice->>'字段名'。

修正后的函数代码 #

-- FUNCTION: public.insertorupdateinvoice(jsonb)

-- DROP FUNCTION IF EXISTS public.insertorupdateinvoice(jsonb);

CREATE OR REPLACE FUNCTION public.insertorupdateinvoice(
    invoice jsonb)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
Declare _invoiceid bigint;
begin
  -- 直接从单个JSON对象提取值插入主表，无需jsonb_array_elements
  insert into invoicemaster (expenseid, invoiceno, transactiondate, totalinvoiceamount, invoicedoc, createdby, createdon)
  select (invoice->>'expenseid')::bigint, 
         (invoice->>'invoiceno')::character, 
         (invoice->>'transactiondate')::date, 
         (invoice->>'totalinvoiceamount')::double precision, 
         (invoice->>'invoicedoc')::character, 
         (invoice->>'createdby')::bigint,
         NOW()
  returning invoiceid into _invoiceid;

  -- 正确引用lineitems数组的每个元素
  insert into lineitemmaster (invoiceid, transactiondate, merchantname, amount, departmentid, policyid, itemdescription, 
                              itemcategory, itemtype, status, isrejected, createdby, createdon)
  select _invoiceid::bigint, 
         (x->>'transactiondate')::date, 
         x->>'merchantname', 
         (x->>'amount')::double precision, 
         (x->>'departmentid')::integer, 
         (x->>'policyid')::integer, 
         x->>'itemdescription', 
         (x->>'itemcategory')::integer, 
         (x->>'itemtype')::integer, 
         (x->>'status')::boolean, 
         (x->>'isrejected')::boolean, 
         (x->>'createdby')::bigint,
         NOW()
  from jsonb_array_elements(invoice->'lineitems') as x;
end;
$BODY$;

ALTER FUNCTION public.insertorupdateinvoice(jsonb)
    OWNER TO postgres;

关键修正点说明 #

• 移除主表插入语句中的jsonb_array_elements(invoice)，直接从传入的单个JSON对象提取字段值。
• 修正所有类型转换的括号位置，确保转换作用于提取出的字段值。
• 调整子表插入时的字段引用方式，直接使用x->>'字段名'访问lineitems数组的每个元素属性。

验证用调用语句 #

select * from insertorupdateinvoice('{"expenseid":1,
    "invoiceno":"04012022",
    "transactiondate":"2022-01-04",
    "totalinvoiceamount":1000.00,
    "invoicedoc":"invoicedoc",
    "createdby":1,
    "lineitems":[ 
      {"transactiondate":"2022-01-01", "merchantname":"Apple", "amount":"100.50", "departmentid":"1","policyid":"1", "itemdescription":"iphone 14 pro max", "itemcategory":"55", "itemtype":"499", "status":"true", "isrejected":"false", "createdby":"1"},
      {"transactiondate":"2022-01-02", "merchantname":"Samsung", "amount":"1050.35", "departmentid":"2","policyid":"2", "itemdescription":"samsung galaxy tab", "itemcategory":"40", "itemtype":"50", "status":"true", "isrejected":"false", "createdby":"1"},
      {"transactiondate":"2022-01-03", "merchantname":"Big bazar", "amount":"555.75", "departmentid":"3","policyid":"3", "itemdescription":"grocerry", "itemcategory":"5", "itemtype":"90", "status":"false", "isrejected":"false", "createdby":"1"}
    ]}');

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