3 Replies Latest reply on Jul 10, 2018 2:04 PM by Steven Hawkins

    Unable to query/import multiple json loops for Rest source

    Srinath Kankanala Newbie

      Hello All,

       

      Im importing Elastic Search index through Import>Teiid Connection >> Source Model>selected the data datasource

      Our Elastic Search index source is json file and elastic search is exposted as Rest API. So im using ws translator to import the Elastic Search index through Rest API.

       

       

      By following the above steps i created a Src Model(invokeHttp). Now i created a table and assigned invokeHttp as the source for the table as below.

       

      In the above SQL Transformation between invokeHttp and Table i wrote custom SQL to select multiple columns from multiple json loops as shown below. CustomerNumber is a column under Segments and number, citycode, longfreetext, type are the columns under PhoneNumbers and PhoneNumbers is under Segments.

       

      Here we have 5 columns in total and they are in different loops and when i preview data i see below error.

       

      Is this issue because im trying to query columns from multiple json loops? if Yes, if there any alternative solution to import multiple columns from multiple json loops and create a model?

       

      Another issue is if i have multiple columns with same name under different loops im not able to import as jboss is throwing duplicate colunns error as shown below. Please let me know how can i handle this.

       

       

      Any help will be very much appreciated.

        • 1. Re: Unable to query/import multiple json loops for Rest source
          Ramesh Reddy Master

          You need to put the root path at "one of" the deepest nesting and have the xpath for other columns relative to that. You can not traverse multiple nestings in a single transformation. For example:

           

          Customer
            - Name
            - Addresses
              - Address
              - Address
            - Orders
              - Order
              - Order

           

          From above you can either choose to traverse Customer's Addresses OR Orders in a single transformation, not both. You are free to define two different transformations (views) one for each and later combine both tables based on some common element/attribute (like PK). From your example, you need to do like

           

          XMLTABLE('/response/hits/hists/_source/segments/PhoneNumbers' PASSING JSONTOXML('response', f.result COLUMNS customernumber string PATH ../customernumber/text(), number string, citycode string ...)

           

          Should do the trick. The same applies for your next question.

           

          Ramesh..

          • 2. Re: Unable to query/import multiple json loops for Rest source
            Srinath Kankanala Newbie

            Thank you for the quick response Ramesh. Earlier i was successful importing multiple columns from single nested loop and was able deploy the VDB and data is available through JDBC and Rest API. I wanted to know if there is a way i can traverse multiple columns from multiple nestings in a single transformation as we have more than 30 nestings and performance will be impacted if we create 30 transformations and join them later.  But based on your response above its clear that we can not traverse multiple columns from multiple nestings in a single transformation. I will check if there is a way we can flatten the json file or minimize the nested loops.

             

            Thanks,

            Srinath.

            • 3. Re: Unable to query/import multiple json loops for Rest source
              Steven Hawkins Master

              XMLTABLE does support retrieving a sequence as an array type as well.  For example if there is an id associated with an address / order you could pull those as an array.