Search code examples
jsonmuleesb

Insert JSON into multiple tables on Database in Mule

I am trying to insert the contents of an JSON to a MySql database using Mule ESB. The JSON looks like: 

{
 "id":106636,
 "client_id":9999,
 "comments":"Credit",
 "salesman_name":"Salvador Dali", 
 "cart_items":[
              {"citem_id":1066819,"quantity":3}, 
              {"citem_id":1066820,"quantity":10}
            ]
}

On mule I want to insert all data on a step like:

  • Insert INTO order_header(id,client_id,comments,salesman_name)
  • Insert INTO order_detail(id,citem_id,quantity)
  • Insert INTO order_detail(id,citem_id,quantity)

Currently i have come this far on Mule: MuleSoft Flow

Solution

  • There is an excellant article here http://www.dotnetfunda.com/articles/show/2078/parse-json-keys-to-insert-records-into-postgresql-database-using-mule that should be of help. You may need to modify as you need to write the order_header data first and then use a collection splitter for the order_detail and wrap the whole in a transaction.