13 Replies Latest reply on Jul 30, 2018 12:11 PM by Ramesh Reddy

    Unable to query/import multiple json loops for Rest source

    Srinath Kankanala Novice

      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 Novice

            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. 

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

                Thank you for the response Steven.  We are still struggling to integrate Elastic Search with Jboss. Our Elk source is json file which has multiple nestings (more than 30 nestings). So Teiid Importer is not able to traverse multiple nestings in a single transformation. In my json file we have more than 30 nestings and i don't want to create 30 transformations, tables and restprocs. Is there any alternate solution to import Elk index and deploy vdb for rest access.

                 

                Any help will be much appreciated.

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

                  Srikanth,

                   

                  I believe we have already given tow different ways you can solve the issue. Steve's suggestion makes you read it "addresses" and "orders" as array values which is a single resultset. Hypothetically, given the above example of "addresses" and "orders", what is your expectation of how the final result set should look like? give me a sample of your expectation.

                   

                  Ramesh..

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

                    So we are trying to import Elk index. Source of Elk index is json file which has multiple nested loops with multiple columns. But when we import index as Invoke Http and create a table we want create just one table for whole index (Multiple nested loops and multiple columns) instead of creating single table for single nested loop.

                     

                    For Example in the below screenshot we created one single table for columns that are from multiple nested loops.

                    pnrid, linenumber are under /response/hits/hits/_source/Remarks/Remark

                    and

                    pnridb, agentsignature are under /response/hits/hits/_source/segments

                    and when we run the data preview we see the error " TEIID30384 Error while evaluating function jsontoxml".

                     

                    So basically we want want to arrive like below create single tables for columns from multiple nested loops. So for pnrid and linenumber data should be fetched from /response/hits/hits/_source/Remarks/Remark

                    and for pnridb, agentsignature data should be fetched from /response/hits/hits/_source/segments

                    Thank you.

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

                      I would  like to see your example with at least two values in each nested loop

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

                        Hello Ramesh,

                         

                        Below is the json file in Snap1 which is the source for Elk index.

                         

                        Snap1:

                         

                        Below snap2 is the tree structure of the above json file in jboss while setting the root path.

                        "/response/hits/hits/_source/Remarks/Remark/" which contains "pnrid, linenumber" attributes and the 3rd snap is for root path " /response/hits/hits/_source/segments/" contains the "pnrid, agentsignature" attributes. (pnrid, linenumber attributes are under _source/Remarks/Remark nested loop and pnrid, agentsignature attributes are under _source/segments nested loop.

                         

                        to achieve above task, the transformation SQL that we used is in snap 4 which is giving us below error but if we use single xml table for either of the root path it works fine. We see error only we try to resolve both root paths in single SQL transformation.

                        .when we run the data preview we see the error " TEIID30384 Error while evaluating function jsontoxml".

                         

                        Snap2:

                        Snap3:

                         

                        Snap4:

                        CloseThank you.

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

                          Now, using above values (at least 2 values from  2 nestings ) build a result that you are expecting. Do not show me single row as above.

                           

                          Ramesh..

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

                            Hi Ramesh,

                             

                            I tried my best to explain the issue im facing in the best possible manner i could with the examples. My requirement is simple to fetch data from 2 different nested loops at same level. In this case remarks and segments are at same level under same loop(_source). 

                             

                            Till now whatever article i found in jboss, i succeeded in extracting values from single nested loop. Just for example i can either fetch data from _source/Remarks/Remark OR _source/segments in a single transformation SQL. However i want to fetch data from both _source/Remarks/Remark AND _source/segments in a single transformation.

                             

                            I apologize if i used wrong terminology which might be making it difficult for you to get the real ask.

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

                              Srikanth,

                               

                              I completely understand the issue, what I am asking is what you expect as a the user the result of putting multiple nestings should be? Just saying that I want all nested loops is in other not enough, as a user writing the application how *you* think result should be? I am asking to give an example with at least two nested loops and at least two values in each.

                               

                              Hope I made myself clear

                               

                              Ramesh.

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

                                Below is the json file which is the source for our ELK index. In this file if you see we have 2 different loops with columns at PNR level.

                                 

                                "_source" : {

                                "Remarks" : {

                                  "Remark" : [ {

                                "pnrid" : "12345", 

                                "linenumber" : "1", 

                                "type" : "10", 

                                "category" : "S", 

                                "contents" : "NR-WN5261404089550" } ] 

                                }}

                                 

                                "segments" :{ 

                                "pnrid" : "12345",  

                                "recordlocator" : "ABCDEF", 

                                "gds" : "1", 

                                "platformid" : "NA", 

                                "securitymanagerid" : "4621234", 

                                "globalcustomernumber" : "6559", 

                                "customernumber" : "6921234567", 

                                "creationofficeid" : "XV3I", 

                                "agentsignature" : "TOA",

                                } }

                                 

                                Below is an example record how we would like to drive the data. We would like to import all the columns from Remark loop and Segments loop and deliver all columns as as 1 record. 

                                 

                                              

                                pnridlinenumbertypecategorycontentspnridrecordlocatorgdsplatformidsecuritymanageridglobalcustomernumbercustomernumbercreationofficeidagentsignature
                                12345110SNR-WN526140408955012345ABCDEF1NA462123465596921234567XV3ITOA

                                 

                                 

                                Please let me know if this answers the question or need any additional information. Thank you for the help Ramesh.

                                 

                                Thanks,

                                Srinath.

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

                                  Not quite yet, single row we know we can do I was looking for at least two values in each of the loops and *all* resulting rows based on those values. I also will appreciate if you can explain how the rows formed and relationship between them in each loop to other. For example remarks row 1 to segmense row 2, and how you came up with flat row 

                                   

                                  Ramesh ..