JSON structure access

[mlopezal]

For the next JASON structure:

{

"result": {

"id": "/en/palencia",

"/common/topic/article": [

{

"id": "/m/02wz7b"

}

]

}

}

How can I get the second id with JSON function ?

I've tried several ways but with no results.

The last one:

SELECT t.* FROM (EXEC FreebaseMQLRead.invokeHttp(action => 'GET', endpoint => INPUT_ENDPOINT)) AS x, XMLTABLE('/FreebaseIDResult/result/common/topic/article/*' PASSING JSONTOXML('FreebaseIDResult', result) COLUMNS FreebaseDescription string PATH 'id') AS t;

Thanks a lot

[shawkins]

The issue is that the element names contain the / character.  When possible just look at the xml structure of the result using something similar to:

SELECT JSONTOXML('FreebaseIDResult', '{"result": {"id": "/en/palencia", "/common/topic/article": [{"id": "/m/02wz7b"}]}}')

You should see the document structure:

<?xml version="1.0" encoding="UTF-8"?><FreebaseIDResult xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><result><id>/en/palencia</id><_u002F_common_u002F_topic_u002F_article><id>/m/02wz7b</id></_u002F_common_u002F_topic_u002F_article></result></FreebaseIDResult>

Note that /common/topic/article when used as an element name must be escaped by the SQL/XML escaping rules to generate a valid xml identifier.  So you'll want to use:

XMLTABLE('/FreebaseIDResult/result/_u002F_common_u002F_topic_u002F_article/' PASSING JSONTOXML('FreebaseIDResult', result) COLUMNS FreebaseDescription string PATH 'id')

I've also updated the docs - https://docs.jboss.org/author/display/TEIID/XML+Functions - to include an example of this instead of just mentioning the escaping effect.

[mlopezal]

You're a master !!! Thanks a lot !!.