Perform JDBC Query to JSON Response

[Anon Anon]

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

[Anon Anon]

The Apply XSL Transformation assertion would be the most appropriate solution resulting in the simplest service policy but it does require knowledge of XSL. Unfortunately, we do not have any XSL stylesheet samples to provide but there are a variety of resources throughout the web that may provide these examples.

 

Another option would be to use modify the original XML before transforming it into JSON: You can use the Evaluate Response XPath assertion to target the XML result of the Perform JDBC Query assertion to select elements for removal using the Add or Remove Elements assertion. An Evaluate Regular Expression assertion could also be used if you are looking to remove specific attributes from specific elements (such as our data type attribute).

A policy might be structured something like this:

1. Perform JDBC Query (output to XML)

2. Evaluate Response XPath (targeting JDBC output)

3. Remove Element (remove XPath result from JDBC output)

4. Evaluate Response XPath (targeting JDBC  output)

5. Evaluate Regular Expression (target JDBC output; remove attributes from XPath result)

It is worth noting that these regular expression and XPath evaluation assertions will have an observable performance impact if done in a highly concurrent manner. At the end of the day, another (possibly easier) solution might be to just assign each column to a context variable and build the JSON message manually in a Set Context Variable assertion. This will avoid all of the JDBC metadata that is in the message that you want to avoid. This is actually the processed used by the API Management engineering team for other complementary products such as the API Developer Portal and the Mobile API Gateway. This technique is used throughout the policies that come with those products.