How to create multiple hierarchy sets in for API output

Hello,

I am having issues structuring the API output i am working in. I have attached both the Source data and the JSON output structure expected in the attachment. I have attached the source file which is an extract of the SQL output. Typically my SQL output would be similar to the source i have attched.

I am trying to group the output by Transaction Header columns, Items as Array Object and Tenders as Array Object.

Source file name attached → transactions_1.txt
Expected JSON Output format attached - > transactions_1_json.txt.

I have also attached the export of the pipeline i could get as far as possible.

Really appreciate any help offered.

Thanks
Aravind N

Transactions_1.txt (1.5 KB)
transactions_1_json.txt (1.8 KB)

Group_Hier.slp (18.1 KB)