Unable to query/import multiple json loops for Rest source

[srinath0533]

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.

[rareddy]

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..

[srinath0533]

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.

[shawkins]

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. 

[srinath0533]

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.

[rareddy]

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..

[srinath0533]

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.

[rareddy]

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

[srinath0533]

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:

Thank you.

[rareddy]

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..

[srinath0533]

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.

[rareddy]

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.

[srinath0533]

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. 

pnrid

linenumber

type

category

contents

pnrid

recordlocator

gds

platformid

securitymanagerid

globalcustomernumber

customernumber

creationofficeid

agentsignature

12345

1

10

S

NR-WN5261404089550

12345

ABCDEF

1

NA

4621234

6559

6921234567

XV3I

TOA

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

Thanks,

Srinath.

[rareddy]

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 ..