Forum Discussion

bradwyatt's avatar
bradwyatt
New Contributor II
5 years ago

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 pu...
json
pipeline
transform
skatpally's avatar
skatpally
Former Employee
5 years ago

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

]

  • bradwyatt's avatar
    bradwyatt
    New Contributor II
    5 years ago

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

    ]