Insert JSON into multiple tables on Database in Mule

Question

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

user3855589 · Accepted Answer · 2016-02-06 12:28:29Z

2

Use Bulk Execute operation of Database Connector. You will insert into multiple tables. for ex :

Query text

Insert INTO order_header(payload.id,payload.client_id,payload.comments,payload.salesman_name);
Insert INTO order_detail(payload.id,payload.cart_items[0].citem_id,payload.cart_items[0].quantity); etc..

answered Feb 6, 2016 at 12:28

user3855589

1,1012 gold badges15 silver badges43 bronze badges

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

Comments

hpandalai · Accepted Answer · 2016-02-05 09:09:05Z

1

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.

answered Feb 5, 2016 at 9:09

hpandalai

4782 gold badges16 silver badges33 bronze badges

Comments

amit · Accepted Answer · 2016-02-04 22:36:18Z

0

Ok. Since, you have already converted JSON into Object in the flow, you can refer individual values with their object reference like obj.id, obj.client_id etc.

Get a database connector next.
Configure your MySQL database in "Connector Configuration".
Operation: Choose "Bulk execute"
In "Query text" : Write multiple INSERT queries and pass appropriate values from Object (converted from JSON). Remember to separate multiple queries with semicolon (;) in Query text.

That's it !! Let me know if you face any issue. Hope it works for you..

answered Feb 4, 2016 at 22:36

amit

1

2 Comments

Joseph Johnson Over a year ago

I think I expressed myself poorly, the quantity of cart_items if dynamic so i cant do a fixed query for the amount of cart_items.

amit Over a year ago

Ok. Got it !! So, The number of cart_items will determine the number of "INSERT" statements for ORDER_DETAIL table. Try this: 1. Have 1 DB connector post the JSONtoObject connector. 2. Add logic to use only 1 INSERT statement for ORDER_HEADER table. 3. Then, Add a "For each" component that will loop on the collection of cart_items. The Object from JSON should have a collection of cart_items. 4. Add another database component within "For each" and mention 1 INSERT statement for ORDER_DETAIL table. For each will automatically iterate over the cart_item collection and perform required "inserts".

Collectives™ on Stack Overflow

Insert JSON into multiple tables on Database in Mule

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related