postgreSQL - ERROR: cannot extract elements from an object

Question

I am a newbie to PostgreSQL. I have the below function and it is giving me an error. basically, I am passing JSON as a parameter to the function, extracting values, and inserting it into 2 tables. it is one to many relationships. so based on that one invoice has multiple line items:

PostgreSQL function:

    -- 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
      insert into invoicemaster (expenseid, invoiceno, transactiondate, totalinvoiceamount, invoicedoc, createdby, createdon)
      select (j.invoice->>'expenseid')::bigint, 
             j.invoice->>'invoiceno'::character, 
             (j.invoice->>'transactiondate')::date, 
             (j.invoice->>'totalinvoiceamount')::double precision, 
             j.invoice->>'invoicedoc'::character, 
             (j.invoice->>'createdby')::bigint,
             NOW()
      from jsonb_array_elements(invoice) as j(invoice)
      returning invoiceid into _invoiceid;

      insert into lineitemmaster (invoiceid, transactiondate, merchantname, amount, departmentid, policyid, itemdescription, 
                                  itemcategory, itemtype, status, isrejected, createdby, createdon)
      select _invoiceid::bigint, 
            (x.invoice->>'transactiondate')::date, 
            x.invoice->>'merchantname', 
            (x.invoice->>'amount')::double precision, 
            (x.invoice->>'departmentid')::integer, 
            (x.invoice->>'policyid')::integer, 
            x.invoice->>'itemdescription', 
            (x.invoice->>'itemcategory')::integer, 
            (x.invoice->>'itemtype')::integer, 
            (x.invoice->>'status')::boolean, 
            (x.invoice->>'isrejected')::boolean, 
            (x.invoice->>'createdby')::bigint,
            NOW()
      from jsonb_array_elements(invoice ->'lineitems') as x;
    end;
    $BODY$;

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

calling function as :

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"}
    ]}');

getting error as below:

ERROR:  cannot extract elements from an object
CONTEXT:  SQL statement "insert into invoicemaster (expenseid, invoiceno, transactiondate, totalinvoiceamount, invoicedoc, createdby, createdon)
      select (j.invoice->>'expenseid')::bigint, 
             j.invoice->>'invoiceno'::character, 
             (j.invoice->>'transactiondate')::date, 
             (j.invoice->>'totalinvoiceamount')::double precision, 
             j.invoice->>'invoicedoc'::character, 
             (j.invoice->>'createdby')::bigint,
             NOW()
      from jsonb_array_elements(invoice) as j(invoice)
      returning invoiceid"
PL/pgSQL function insertorupdateinvoice(jsonb) line 5 at SQL statement
SQL state: 22023

Thanks

invoice is an object - not an array. Hence jsonb_array_elements throws an error — madflow
– madflow, Commented Jan 5, 2023 at 13:19
ok, I placed square brackets as below: ``` select * from insertorupdateinvoice('[ {"expenseid":1,... ]} ]'); ``` it is giving me below error: ``` ERROR: column x.invoice does not exist LINE 4: (x.invoice->>'transactiondate')::date, ^ QUERY: insert into lineitemmaster (invoiceid, transactiondate, ...,createdon) select _invoiceid::bigint, (x.invoice->>'transactiondate')::date, ........... NOW() from jsonb_array_elements(invoice ->'lineitems') as x; ``` — Krunal
– Krunal, Commented Jan 5, 2023 at 13:27
I think it would be best, when you create a reproducible DB fiddle with this: db-fiddle.com . I would think you do not need jsonb_array_elements at all. Focus on what the input data is and what the desired outcome is. — madflow
– madflow, Commented Jan 5, 2023 at 13:34

Edouard · Accepted Answer · 2023-01-09 08:32:58Z

1

try this :

CREATE OR REPLACE FUNCTION public.insertorupdateinvoice(
    invoice jsonb)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
Declare _invoiceid bigint;
begin
  insert into invoicemaster (expenseid, invoiceno, transactiondate, totalinvoiceamount, invoicedoc, createdby, createdon)
  values ( (invoice->>'expenseid') :: bigint, 
           invoice->>'invoiceno', 
           (invoice->>'transactiondate') :: date, 
           (invoice->>'totalinvoiceamount') :: double precision, 
           invoice->>'invoicedoc', 
           (invoice->>'createdby') :: bigint,
           NOW()
         )
  returning invoiceid into _invoiceid;

  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$;

see dbfiddle

edited Jan 9, 2023 at 8:32

answered Jan 7, 2023 at 10:05

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Krunal Over a year ago

Thanks, this works for me. but for 'invoicemaster' table, invoiceno takes 0 instead '04012022' and invoicedoc columns take only first letter 'i' instead 'invoicedoc' and stored in db table. Please execute both the tables in the same fiddles.

Edouard Over a year ago

i've updated the answer according to your comment

Krunal Over a year ago

thanks, it is working now and storing full-length value.

Krunal Over a year ago

Hi @Edouard, today when I ran the function, it is not extracting json values and saving to for "invoicemaster" master table. nothing is changed so working yesterday not working today.

Collectives™ on Stack Overflow

postgreSQL - ERROR: cannot extract elements from an object

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related