2 Replies Latest reply on Jul 10, 2012 1:31 PM by Mónica L.A.

    JSON structure access

    Mónica L.A. Newbie

      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

        • 1. Re: JSON structure access
          Steven Hawkins Master

          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.

          • 2. Re: JSON structure access
            Mónica L.A. Newbie

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