How to Transform SQL Server Records to JSON usable format

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

It can be achieve this through Velocity Template.

In the JSON Generator snap you can put the template of your choice and refer the field names from the upstream.

Sample

[
{
“rootdoc”:
{
“fieldname1”:$value
}
}

]

Thanks for the response.

If I do your suggestion, the output would be:
[
{
“rootdoc”:
{
“fieldname1”:VALUE1
}
},
{
“rootdoc”:
{
“fieldname1”:VALUE2
}
}

]

But, I would rather want the following output:
[
{
“rootdoc”:
[{
“fieldname1”:VALUE1
},
{
“fieldname1”:VALUE2
}]
}

]

That’s just a sample. We can do achieve the desired o/p with looping in Velocity Template.

Appreciate the help so far. Can you give me an example snippet (with looping as you mentioned) using Velocity that is similar to what I’m asking for? Or if you have no examples, how would you use Velocity to code the below?

[
{
“rootdoc”:
[{
“fieldname1”:VALUE1
},
{
“fieldname1”:VALUE2
}]
}

]

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)

image

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.

4 Likes

That worked, thank you so much!