6 Replies Latest reply on Apr 20, 2018 1:35 PM by Steven Hawkins

    Teiid import Json

    parrotola Novice

      Hi,

      how can I create a datasource from Json file?

       

      thank you for all.

       

      Francesco

        • 1. Re: Teiid import Json
          Ramesh Reddy Master

          If the JSON is file, use the "file" translator for access, if it is web service then use "ws" for access to the contents of the JSON file. Then you would need to convert JSON to XML, and use XMLTABLE to convert to a relational table. A example here Using JSON array in teiid - Stack Overflow

           

          Note the above example used a literal string as JSON content, you would need to replace respective parts of query with file or ws access routines. I believe there is a thread in forums showing an example too.

           

          See Data source - REST API with JSON format

          Teiid - Consume JSON tree from REST web service

          • 2. Re: Teiid import Json
            parrotola Novice

            Hi Ramesh,

            I tried this code:

             

            SELECT

            x.firstName, x.lastName,'ggg' 

            FROM XMLTABLE('/response/response' passing

            jsontoxml('response',

            jsonparse('

            [{"firstName" : "George", "lastName" : "Micheal" },

            { "firstName" : "Jerry", "lastName" : "Springer" }]', TRUE)) COLUMNS firstname string path firstname, lastname string) as x

            but I have retrieved two empty rows.

            At the same way I tried to create the same query with a file as source:

             

            SELECT

            x.firstname, x.lastname

            FROM

            (EXEC xmlSourceModel.getTextFiles('test.json')) AS f, XMLTABLE('/response/response' PASSING jsontoxml('response', jsonparse(f.file, TRUE)) COLUMNS firstname string, lastname string) AS x

            and I retrieved two empty rows again.

             

            Thank you for all.

             

            Best regards,

             

            FRancesco

            • 3. Re: Teiid import Json
              Ramesh Reddy Master

              This worked fine for me

               

              SELECT
              x.firstName, x.lastName,'ggg' 
              FROM XMLTABLE('/response/response' passing
              jsontoxml('response',jsonparse('[{"firstName" : "George", "lastName" : "Micheal" },{ "firstName" : "Jerry", "lastName" : "Springer" }]', TRUE)) 
              COLUMNS firstname string path 'firstName', lastName string) as x
              • 4. Re: Teiid import Json
                parrotola Novice

                Hi Ramesh,

                 

                Thank you for you answer.

                But I have another problem with json that it has a space in key value?

                For example If I execute this query I retrieve this error message:

                 

                SELECT

                x.firstName, x.lastName,'ggg'

                FROM XMLTABLE('/response/response' passing 

                jsontoxml('response',jsonparse('[{"first Name" : "George", "lastName" : "Micheal" },{ "first Name" : "Jerry", "lastName" : "Springer" }]', TRUE))  

                COLUMNS firstname string path 'first Name', lastName string) as

                 

                SQL Error [30155] [50000]: TEIID30155 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30155 Column "firstname" has an invalid path expression: first Name

                  TEIID30155 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30155 Column "firstname" has an invalid path expression: first Name

                    TEIID30155 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30155 Column "firstname" has an invalid path expression: first Name

                      javax.xml.transform.TransformerException: Remote net.sf.saxon.trans.XPathException: Unexpected token name "Name" beyond end of expression.

                 

                 

                Thank you for all.

                Best regards.

                 

                 

                francesco

                • 5. Re: Teiid import Json
                  Ramesh Reddy Master

                  I am not sure there is support for space in the key currently in Teiid. You can log a JIRA for enhancement. shawkins  is this allowed?

                  • 6. Re: Teiid import Json
                    Steven Hawkins Master

                    You can have a space in the json key.  You cannot have a space in the xpath - it's not a legal path.  The jsontoxml logic already will convert invalid identifiers.  If you just run:

                     

                    select jsontoxml('response',jsonparse('[{"first Name" : "George", "lastName" : "Micheal" },{ "first Name" : "Jerry", "lastName" : "Springer" }]', TRUE)) 

                     

                    You'll see: first_x0020_Name as the element name.

                     

                    Due to [TEIID-5294] Bug with the name correction logic (TEIID30151 eror) - JBoss Issue Tracker in older versions this would have been _u0020_.  You should use the corrected name as the path.