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

[skatpally]

It can be achieve this through Velocity Template.

velocity.apache.org

Apache Velocity Engine - User Guide

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

]

[bradwyatt]

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

]

[skatpally]

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

[bradwyatt]

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

]