โ03-23-2021 07:11 PM
I am querying transactions_table and batch_table using SQL Server Execute snap. The batch_table returns 1 record, and in this example the transactions_table returns 2 records. I need to be able to pull the data into a JSON-format that is compatible with my companyโs API.
Below is an example format. The โinterfaceโ keyโs values are manually entered by me. The โbatch_tableโ key contains the values from the one record in the batch_table from SQL Server. The โtransactions_tableโ key contains a list of all the records from the transactions_table.
What would be the most efficient way of implementing this?
{
"interface" : {
"type" : "INVOICE",
"client": SQLServer
},
"data": {
"batch_table": {
"BATCH_ID": 1
},
"transactions_table": [{
"TRANSACTION_ID": 1,
"AMOUNT": 5.90
},
{
"TRANSACTION_ID": 2,
"AMOUNT: 11.20
}]
}
}
โ04-05-2021 02:42 AM
Hi ,
Here is one example how can you achieve the result as in your example in the question above.
{
โinterfaceโ: {
โtypeโ: โINVOICEโ,
โclientโ: โSQLServerโ
},
โdataโ: {
โbatch_tableโ: {
โBATCH_IDโ: 1
},
โtransactions_tableโ: [{
โTRANSACTION_IDโ: 1,
โAMOUNTโ: 5.90
}, {
โTRANSACTION_IDโ: 2,
โAMOUNTโ: 11.20
}
]
}
}
Transform_SQL_JSON_Results.slp (5.0 KB)
In the pipeline, I took the example data, and transformed it as csv, so I put it inside โCSV Generatorโ snap.
Later, in order to form the array for โtransactions_tableโ, with snap โGroup By Fieldsโ, the data is grouped by type, client and batch_id, so the result looks like this
At the end, with the snap โJSON Generatorโ the output is formed as you expected to be, using apache velocity
I hope this will resolve your problem.
Regards,
Angela.
โ04-08-2021 05:12 PM
That worked, thank you so much!