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 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
        }]
    }
}
json
pipeline
transform

6 Replies

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

      ]

  • skatpally's avatar
    skatpally
    Former Employee
    5 years ago

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

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

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

      ]

      • angie's avatar
        angie
        New Contributor
        5 years ago

        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.