MuleSoft Database Connector Insert Nested Array

Question

I have a object which has nested arrays and i need to insert that payload into json . Can anyone help me with this

Here is the SQL:

INSERT INTO `sys`.`order` (`po_date`, `company_name`, `buyer_name`, `buyer_phone`, `company_address`, `company_city`, `company_state`, `company_postal_code`, `shipment_address`, `shipment_city`, `shipment_state`, `shipment_postal_code`, `po_number`) 
    VALUES (:po_date, :company_name, :buyer_name, :buyer_phone, :company_address, :company_city, :company_state, :company_postal_code, :shipment_address, :shipment_city, :shipment_state, :shipment_postal_code, :po_number);
INSERT INTO `sys`.`item` (`name`, `price`, `quantity`, `item_number`) 
    VALUES (:item_name, :item_price, :item_quantity, :item_number);
INSERT INTO `sys`.`order_item` ( `order_ref`, `item_ref`) 
    VALUES ( :order_ref, :item_ref);

and here is the input that im sending to database.

{
  "po_date": "2/5/2020",
  "company_name": "McCullough, Howe and Graham",
  "buyer_name": "Jeno MacCracken",
  "buyer_phone": "518-508-5392",
  "shipment_postal_code": "12237",
  "po_number": "16-492-5465",
  "items": [
    {
      "item_name": "43-583-0978",
      "item_price": "Sodium Chloride",
      "item_quantity": "26",
      "item_number": "802.41"
    },
    {
      "item_name": "71-788-5293",
      "item_price": "Ciprofloxacin",
      "item_quantity": "100",
      "item_number": "608.64"
    }
  ],
  "order_items": [
    {
      "order_ref": null,
      "item_ref": "43-583-0978"
    },
    {
      "order_ref": null,
      "item_ref": "71-788-5293"
    }
  ]
}

Alex Alex · Accepted Answer · 2020-05-03T02:39:40

I can help you but I need mode details. Is it Mule 4 or Mule 3? Where are missing parameters like company_address?

In general in Mule 4 you should create Insert statements which look like this:

<db:insert doc:name="Insert" doc:id="639efcf2-6422-41c7-b533-b558179efeb9" config-ref="Database_Config">
    <db:sql >INSERT INTO `sys`.`order` (`po_date`, `company_name`, `buyer_name`, `buyer_phone`, `company_address`, `company_city`, `company_state`, `company_postal_code`, `shipment_address`, `shipment_city`, `shipment_state`, `shipment_postal_code`, `po_number`)

VALUES (:po_date, :company_name, :buyer_name, :buyer_phone, :company_address, :company_city, :company_state, :company_postal_code, :shipment_address, :shipment_city, :shipment_state, :shipment_postal_code, :po_number)

where you provide Insert SQL and parameters below in separate window

That's for first statement which is alone. For two other statements you do the same but as Bulk Insert where SQLs look similar to yours but inputs are arrays from your payload

whole flow should look like this

Actually I do not see how these 3 SQLs are connected. I would expect some usage of general data (like shipment_postal_code) saved for each order. But it is not in your SQLs.

It would be easy to do by merging array items as described here https://simpleflatservice.com/mule4/MergeArraysItems.html and then using array of these merged items to insert items to the database.

MuleSoft Database Connector Insert Nested Array

1 Answers