Multiple MySQL tables to nested JSON

One Star

Multiple MySQL tables to nested JSON

Hello,
Is there an example somewhere for joining two mysql tables into a nested JSON file? Basically what I am trying to do is take an orders table, and an order_items table and combine them into a single JSON file, but where things run into issues is the 1:n relationship between orders and order_items.
As a very basic example:
ORDER
--------------
ID    | USER_ID
--------------
1    | 123
2    | 456

ORDER_ITEM
---------------------------
ID     | ORDER_ID    | SKU
---------------------------
10    | 1            | ABCDE
11    | 2            | ABCDE
12    | 2            | FGHIJ

OUTPUT:
orderID: 1
userID: 123
    orderItemID: 10
    orderItemSKU: ABCDE
orderID: 2
userID: 456
    orderItemID: 11
    orderItemSKU: ABCDE
    orderItemID: 12
    orderItemSKU: FGHIJ   

JSON is ideal, but if this can be done in XML and converted to JSON that would work too.
Appreciate the help!
Moderator

Re: Multiple MySQL tables to nested JSON

Hi,
Have you already checked component:TalendHelpCenter:tWriteJSONField which transforms the incoming data into JSON fields?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Multiple MySQL tables to nested JSON

Have you already checked component:TalendHelpCenter:tWriteJSONField which transforms the incoming data into JSON fields?

Hi Sabrina, yeah, I found that late last night and then setting the "Group by" column to the order ID, and the loop in the tree, I was able to get everything working. Still some regex to do before the output is useful in an tHttpRequest POST (which now suffers from https://jira.talendforge.org/browse/TDI-31574), but definitely past the hurdle.
This was one of those, spend 4h trying to figure it out, post to the forum, and then figure it out. sigh. Appreciate the help as always.
Cheers,
One Star

Re: Multiple MySQL tables to nested JSON

Follow-up related to the tWriteJSONField... Is it possible to force arrays (other than a brutal regex hack)?
For example, there is a loop for the "options" object which builds an options array:

"options":

When there is only one option however, it outputs:
"options":{"name":"Colour","value":"Brown"}

Is it possible to force this?:
"options":

The API where this JSON is eventually used is kicking back the following error and I'm not sure how to get around it:
Cannot deserialize the current JSON object because the type requires a JSON array (e.g. ) to deserialize correctly.