AnsweredAssumed Answered

Perform JDBC Query to JSON Response

Question asked by UoC on Jul 16, 2015
Latest reply on Jul 20, 2015 by goeer03

Hi All.


I would really appreciate some advise and help with this transformation which I imagine is fairly 'standard'.


On the CA API Gateway, I generate XML Result from the Perform JDBC Query Policy (from sql Server)


<?xml version="1.0" encoding="UTF-8"?>

<L7j:jdbcQueryResult xmlns:L7j="http://ns.l7tech.com/2012/08/jdbc-query-result">

    <L7j:row>

        <L7j:col  name="buildingId" type="java.lang.Integer">343</L7j:col>

        <L7j:col  name="buildingCode" type="java.lang.String">AR01</L7j:col>

        <L7j:col  name="buildingDescription" type="java.lang.String">Logie</L7j:col>

        <L7j:col  name="buildingSite" type="java.lang.String">Ilam</L7j:col>

        <L7j:col  name="buildingMaxFloorLevel" type="java.lang.Integer">6</L7j:col>

    </L7j:row>

    <L7j:row>

        <L7j:col  name="buildingId" type="java.lang.Integer">344</L7j:col>

        <L7j:col  name="buildingCode" type="java.lang.String">AR02</L7j:col>

        <L7j:col  name="buildingDescription" type="java.lang.String">Locke</L7j:col>

        <L7j:col  name="buildingSite" type="java.lang.String">Ilam</L7j:col>

        <L7j:col  name="buildingMaxFloorLevel" type="java.lang.Integer">8</L7j:col>

    </L7j:row>

</L7j:jdbcQueryResult>

 

I apply a JSON Transformation and to get the Response

{

  "L7j:jdbcQueryResult": {

    "L7j:row": [

      {

        "L7j:col": [

          {

            "content": "343",

            "name": "buildingId",

            "type": "java.lang.Integer"

          },

          {

            "content": "AR01",

            "name": "buildingCode",

            "type": "java.lang.String"

          },

          {

            "content": "Logie",

            "name": "buildingDescription",

            "type": "java.lang.String"

          },

          {

            "content": "Ilam",

            "name": "buildingSite",

            "type": "java.lang.String"

          },

          {

            "content": "6",

            "name": "buildingMaxFloorLevel",

            "type": "java.lang.Integer"

          }

        ]

      },

      {

        "L7j:col": [

          {

            "content": "344",

            "name": "buildingId",

            "type": "java.lang.Integer"

          },

          {

            "content": "AR02",

            "name": "buildingCode",

            "type": "java.lang.String"

          },

          {

            "content": "Locke",

            "name": "buildingDescription",

            "type": "java.lang.String"

          },

          {

            "content": "Ilam",

            "name": "buildingSite",

            "type": "java.lang.String"

          },

          {

            "content": "8",

            "name": "buildingMaxFloorLevel",

            "type": "java.lang.Integer"

          }

        ]

      }

    ],

    "xmlns:L7j": "http://ns.l7tech.com/2012/08/jdbc-query-result"

  }

}

 

However, I Would Like to format the JSON response like this

{  

    "data": [

        {

            "buildingId": "343",

            "buildingCode": "AR01",

            "buildingDescription": "Logie",

            "buildingSite": "Ilam",

            "buildingMaxFloorLevel": "6"

        },

        {

            "buildingId": "344",

            "buildingCode": "AR02",

            "buildingDescription": "Logie",

            "buildingSite": "Ilam",

            "buildingMaxFloorLevel": "8"

        }

    ]

}

 

Would someone or does someone has a good example of how to do this or something similar with the Apply xsl Transform? Is there a recommended location for examples?

 

Would a better option be to build this response from context variables that are set within the Perform JDBC Query Policy? If so, whats the best way to build the JSON from this? (I imagine if there are commas in the data this might be a problem?)

 

Another option could be that I build the JSON Response for each Row in the actual query (stored procedure) by concatenation into a JSON String?

 

I think the xsl option would be the 'recommended' and shouldn't be to difficult but I cant find any good examples of this type of transform although I would expect it to be fairly common.

 

Help, advise and experience will be much appreciated. We are just getting underway with the API Gateway and are under time constraints to deliver our Pilot soon.

 

Many Thanks,

Paul

Outcomes