12 Replies Latest reply on Jun 30, 2014 6:01 PM by Masilamani Subramanyam

    Data source - REST API with JSON format

    lightsail pro Newbie

      I have a few data sources (Facebook, Twitter, Elastic Search...) using REST API but with JSON format. From my understanding, in Teiid, we can use the importer - "File Source (XML) >> Source and View Model - XML file via remote URL" for the data sources that support REST API with XML format. Do we have any solution for the JSON format with current 8.7 + 8.5 designer release? If no, is the new feature in pipeline? Thanks in advance

        • 1. Re: Data source - REST API with JSON format
          Ramesh Reddy Master

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

          • 2. Re: Data source - REST API with JSON format
            Ted Jones Master

            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

            • 3. Re: Data source - REST API with JSON format
              Masilamani Subramanyam Newbie

              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

              • 4. Re: Re: Data source - REST API with JSON format
                Ramesh Reddy Master

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

                • 5. Re: Data source - REST API with JSON format
                  Steven Hawkins Master

                  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.

                  • 6. Re: Data source - REST API with JSON format
                    Masilamani Subramanyam Newbie

                    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

                    • 7. Re: Data source - REST API with JSON format
                      Masilamani Subramanyam Newbie

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

                      • 8. Re: Data source - REST API with JSON format
                        Masilamani Subramanyam Newbie

                        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

                        • 9. Re: Data source - REST API with JSON format
                          Steven Hawkins Master

                          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.

                          • 10. Re: Data source - REST API with JSON format
                            Masilamani Subramanyam Newbie

                            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

                            • 11. Re: Data source - REST API with JSON format
                              Steven Hawkins Master

                              > 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

                              • 12. Re: Data source - REST API with JSON format
                                Masilamani Subramanyam Newbie

                                Steven,

                                 

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