Teiid import Json

[parrotola]

Hi,

how can I create a datasource from Json file?

thank you for all.

Francesco

[rareddy]

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

[parrotola]

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

[rareddy]

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

[parrotola]

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 x 

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

[rareddy]

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?

[shawkins]

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.