How to create multiple hierarchy sets in for API output


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.

Aravind N

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

Group_Hier.slp (18.1 KB)

In you data, what is the thing which determines if it is an item or tender?

This pipleine does what you want I think. I put the inoput data in the CSV generator at the beginning, and the output of the join give the data you need.
Take a look.

Group_test_2019_03_23 (1).slp (23.7 KB)

1 Like

Thanks for the Solution. The Solution is almost what i need except the "Transaction_id should be displayed at the header level only. “The tenders” child array also displays the “TRANSACTION_ID” attribute which i don’t want to display at the tenders level.

I tried removing by having empty expression variable for “transactiod_id” in the “Remove groupBy tenders” Snap but didn’t work. It would be really helpful if this is sorted.


receipt_no,item_id,quantity,item_total,item_gross,item_tax are item attributes grouped for each transaction_id.

media_id,media_no,media_value are tenders attributes grouped for each transaction_id

Add a line in the mapper “Remove GroupBy” to remove that:
Updated pipeline: Group_test_2019_03_25.slp (23.8 KB)
Updated Output: Join.txt (2.6 KB)

1 Like

That worked perfect. Thank you so much!