Multiple MySQL tables to nested JSON

[Anonymous]

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!

[Anonymous]

Hi,
Have you already checked component:TalendHelpCenter:tWriteJSONField which transforms the incoming data into JSON fields?
Best regards
Sabrina

[Anonymous]

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,

[Anonymous]

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.