How to create multiple hierarchy sets in for API output

[arvindnsn]

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)

[cstewart]

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

[cstewart]

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)

[arvindnsn]

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.

Thanks

[cstewart · Accepted solution]

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)