How to grab the value from JSON object in Postgresql and insert into some other tables?

{
  "orderId": "s",
  "fulfillerId": "qaqj8fkbmb",
  "orderDetailsUrl": "asd",
  "items": [
    {
      "dTe": "laseIn",
      "itemDescription": "Tetesting",
      "itemId": "item_1",
      "manufacturingUrl": "https://uploads_1.documents.press",
      "skuCode": "1223",
      "productName": "Tesing",
      "quantity": 225,
      "taskId": "task_ID_1"
    },
    {
      "dTe": "laseIn",
      "itemDescription": "Test Sku for Oracle testing",
      "itemId": "item_2",
      "manufacturingUrl": "https://uploads_2.documents.press",
      "skuCode": "123",
      "productName": "Test Sku for Oracle testing",
      "quantity": 225,
      "taskId": "task_ID_2"
    }
  ]
}

implement

-> grab value of orderId/fulfillerId/orderDetailsUrl and perform iteration for item array object to grab required values as its array of similar objects

-> Store in a variable

-> then perform the insert operation

Somehow its not working

Desired Output:

Item Table

• itemId

• fullfillerId

• orderId

• skuCode

++ all remaining data from JSON in Item Table

ItemDetails Table

• itemId

• orderDetailsUrl

• taskId

• quantity

I have tried various jsonb formatting but exact output is not achieved after parsing, I am not able to pick values from JSON and use Insert query

Answer

Here is a plpgsql block for illustration how you may do it. raise notice would be replaced with the relevant command(s), insert for example.

do language plpgsql
$$
declare 
j constant json default $json$
{
  "orderId": "s",
  "fulfillerId": "qaqj8fkbmb",
  "orderDetailsUrl": "asd",
  "items": [
    {
      "dTe": "laseIn",
      "itemDescription": "Tetesting",
      "itemId": "item_1",
      "manufacturingUrl": "https://uploads_1.documents.press",
      "skuCode": "1223",
      "productName": "Tesing",
      "quantity": 225,
      "taskId": "task_ID_1"
    },
    {
      "dTe": "laseIn",
      "itemDescription": "Test Sku for Oracle testing",
      "itemId": "item_2",
      "manufacturingUrl": "https://uploads_2.documents.press",
      "skuCode": "123",
      "productName": "Test Sku for Oracle testing",
      "quantity": 225,
      "taskId": "task_ID_2"
    }
  ]
}
$json$;
r record;
begin
    -- Item
    for r in select j->>'fulfillerId' fullfillerid, j->>'orderId' orderid, t->>'itemId' itemid, t->>'skuCode' skucode
     from json_array_elements(j -> 'items') t loop
        raise notice '% % % %', r.fullfillerid, r.orderid, r.itemid, r.skucode;
    end loop;
    --ItemDetails
    for r in select j->>'orderDetailsUrl' orderdetailsurl, t->>'itemId' itemid, t->>'taskId' taskid, (t->>'quantity')::numeric quantity
     from json_array_elements(j -> 'items') t loop
        raise notice '% % % %', r.orderdetailsurl, r.itemid, r.taskid, r.quantity;
    end loop;
end;
$$;

An insert will be simpler, w/o a loop:

insert into target_table ( list - of - columns ) 
  select 
    j->>'orderDetailsUrl' orderdetailsurl, 
    t->>'itemId' itemid, 
    t->>'taskId' taskid, 
    (t->>'quantity')::numeric quantity
    -- other expressions maybe
  from json_array_elements(j -> 'items') t;

As a function that inserts into item table and itemietails table:

create or replace function json_items(raw_data json)
returns void language plpgsql as
$$
begin
 insert into itemdetails(itemid, orderdetailsurl, taskid, quantity) 
  select 
    t->>'itemId', 
    raw_data->>'orderDetailsUrl', 
    t->>'taskId', 
    (t->>'quantity')::numeric
  from json_array_elements(raw_data  -> 'items') t;

 insert into item(itemid, fullfillerid, orderid, skucode, dte, itemdescription)
  select 
    t->>'itemId', 
    raw_data->>'fullfillerId', 
    raw_data->>'orderId', 
    t->>'skuCode',
    t->>'dte',
    t->>'itemDescription'
  from json_array_elements(raw_data  -> 'items') t;
end;
$$;