How to Transform SQL Server Records to JSON usable format

[bradwyatt]

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
        }]
    }
}

[angie]

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.

[bradwyatt]

That worked, thank you so much!