Lightsail Pro,

Teiid offers a function called "JSONTOXML" see JSON Functions - Teiid 8.7 - Project Documentation Editor. Essentially you follow the same procedure as XML and retrieve the content using file or ws translators, then wrap the content expression with JSONTOXML to convert to XML, then XMLTABLE will do the rest of the job extracting the tabular format.

I do not think there is way mention that the data returned is JSON type in the Designer during the import, you would need to design as if it is returning XML then change the transformation after the import wizard is finished. I suggest you log a enhancement JIRA in the Teiid Designer project where distinction can be made about the JSON vs XML data during the import.

Ramesh..

Hi Lightsail pro,

We have JSON import support slated for the 8.6 release: [TEIIDDES-1882] Add Support for JSON Based Web Service Importing - JBoss Issue Tracker

Thanks,

Ted

Hi Ramesh,

We have similar requirement to consume data source using REST API with JSON format so i tried the below steps as per your suggestion to wrap the content using JSONTOXML and then XMLTABLE for extracting in Tabular format.

- created data source with end point as http://www.telize.com/geoip

- created dynamic vdb using above data source

- ran the below query which is throwing an error. 

select t.*

from  ( call geoip.invoke(action   =>'GET')  

         )  as w,

         XMLTABLE('geoip/' passing JSONTOXML('geoip', w.result) columns timezone string path '@timezone' ) as t

ERROR:

The function 'JSONTOXML('geoip', w.result)' is a valid function form, but the arguments do not match a known type signature and cannot be converted using implicit type conversions.

SQLState:  ERR.015.008.0040

Can you please check whether this is what your suggestion for consuming REST API with JSON format and see why this example doesn't work ?

geoip-ds.xml

-------------------

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

<connection-factories>

   <no-tx-connection-factory>

      <jndi-name>geoip-ds</jndi-name>

      <rar-name>teiid-connector-ws.rar</rar-name>

      <connection-definition>javax.resource.cci.ConnectionFactory</connection-definition>     

      <config-property name="EndPoint">http://www.telize.com/geoip</config-property>     

      <max-pool-size>20</max-pool-size>

    </no-tx-connection-factory>

</connection-factories>

geoip-vdb.xml

---------------------

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

<vdb name="geoip" version="1">

    <description>Shows how to call Web Services</description>

    <property name="UseConnectorMetadata" value="cached" />

    <model name="geoip">

        <source name="geoip" translator-name="Messagegeoip" connection-jndi-name="java:geoip-ds"/>

    </model>

    <translator name="Messagegeoip" type="ws">

  <property name="DefaultBinding" value="HTTP"/>

  <property name="DefaultServiceMode" value="MESSAGE"/>

    </translator>

</vdb>

Thanks, Mani

try

CREATE VIRTUAL PROCEDURE
BEGIN
  SELECT t.timezone AS id FROM (EXEC geoip.invokeHttp('GET', null, 'http://www.telize.com/geoip', 'TRUE')) AS f,
      XMLTABLE('/geoip' PASSING JSONTOXML('geoip', f.result) COLUMNS timezone string PATH 'timezone') AS t;
END

Ramesh..

To clarify invoke returns a SQL/XML result, whereas invokeHttp returns a blob result.  So invoke wouldn't be use with JSONToXML as you would already have an xml result from a soap service.

Hi Ramesh,

Thanks.   I ran the query and for some reasons, without specifying parameter name to invokeHttp was throwing an error "Message:Incorrect number of parameters specified on the stored procedure geoip.invokeHttp - expected 3 but got 4" but adding parameter name to invokeHttp and with other changes as you suggested worked fine.

SELECT t.timezone AS id

FROM (EXEC geoip.invokeHttp(action          =>  'GET',

                                               request        =>   null,

                                               endpoint      =>  'http://www.telize.com/geoip',

                                               contentType =>  'TRUE'

                                              )

          ) AS f, 

          XMLTABLE('/geoip' PASSING JSONTOXML('geoip', f.result) COLUMNS timezone string PATH 'timezone') AS t; 

Thanks for your help.

Thanks, Mani

Thanks Steven for clarification for using invokeHttp instead of invoke to use with JSONToXML.

Ramesh and Steven,

Is it possible to intercept to replace/get ride of some values from result of invokeHttp using any functions or regular expression before passing result to JSONTOXML('geoip', f.result) ?   The reason for asking is that we noticed when we invoke REST service from Jive, the JSON response has first line throw 'allowIllegalResourceCall is false.';  as shown below, makes invalid JSON response causing JSONTOXML function call to fail.

JSON response:

throw 'allowIllegalResourceCall is false.';

{
  "id" : "1264",
   ..
   ..
}

So need to get rid of "throw 'allowIllegalResourceCall is false.';" and then pass below JSON response to JSONTOXML.

{
  "id" : "1264",
   ..
   ..
}

Thanks, Mani

It seems that this is a common problem with JIVE.  If the response is small enough to be a string (typically 4000 characters), then you can cast it to a string - "cast(to_chars(f.result, 'UTF-8') as string)" and then use what ever string manipulation on it that you need.

Steven,

Thanks.  We were able to replace "throw 'allowIllegalResourceCall is false.';" from JSON response using cast(to_chars(.... and replace function.    But we noticed that JSONTOXML is not transforming correctly when JSON response include arrays with children list as param:value.   For example,

SELECT cast(JSONTOXML('result', '{"firstName" : "John" , "children" : [ {"firstName": "Randy"}, {"firstName":"Judy"} ]}') as string)

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

<result>

       <firstName>John</firstName>

       <children><children>

       <firstName>Randy</firstName>

       </children>

                 <firstName>

                           <firstName>Judy</firstName>

                 </firstName>

       </children>

</result>

but expected XML document is

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

<firstName>John</firstName>

<result>

  <children>

  <firstName>Randy</firstName>

  </children>

  <children>

  <firstName>Judy</firstName>

  </children>

</result>

Since JSONTOXML is not returning correct XML documents for JSON arrays,  we are not able to consume REST service with JSON output and transform into relational views.

Thanks, Mani

> Since JSONTOXML is not returning correct XML documents for JSON arrays

I'm am seeing the expected output.  What version are you on?  If you are on an older version you could be hitting [TEIID-1896] incorrect xml generated from jsontoxml with nested arrays - JBoss Issue Tracker

Steven,

Thanks for info. We are on version Teiid 7.7.9 and also I have verified in 8.3.2 which works fine.