topic Re: JSON Multiple Loop Elements using tReplicate to tExtractJSONField in Talend Studio

JSON Multiple Loop Elements using tReplicate to tExtractJSONField

Edith1 — Wed, 20 May 2020 23:10:53 GMT

I'm using tRESTClient to call a get REST API. In the JSON response, there's multiple loops and need objects from both loops. I read to split the response data using tReplicate then use tHASHOutput & tHASHInput to combine the data needed. However, I cannot combine them because I need the id# for both tHASHOutput and the id# is only held in the 1st loop. I am using a file JSON for the tExtractJSONFields (see picture below of the mapping).

As shown in the picture below, the output for the id# is all 1 string. I need to extract the id# in the 1st loop then extract the looping elements for that loop. How do I accomplish this?

Re: JSON Multiple Loop Elements using tReplicate to tExtractJSONField

Anonymous — Thu, 21 May 2020 03:53:22 GMT

Hi
Can you please show an example what you extract from loop1 and loop2? and what are you expected result?

Regards
Shong

Re: JSON Multiple Loop Elements using tReplicate to tExtractJSONField

Edith1 — Thu, 21 May 2020 16:19:27 GMT

Thank you Shong for your quick response!

Below is a screenshot of the source schema. There's 2 loops: result[*] and attributes[*]. Those attributes belong to a result.id. For tExtractJSON_1, I am pulling result[*].id and result.name. In addition to this, I need attributeType.name and value from the 2nd loop for tExtractJSON_2. The screenshot below shows the preview where the result.id I am pulling from the 1st loop shows as 1 string for all the ids. The other 2 columns are fine, but I don't know how to split the id data into rows like the other 2.

I'm using the JSON File above and here's a screenshot of tExtractJSONFields_1

Here's a screenshot of tExtractJSONFields_2

Re: JSON Multiple Loop Elements using tReplicate to tExtractJSONField

Edith1 — Tue, 26 May 2020 23:02:50 GMT

Hi, just checking in to see if you need anything else from me.

Re: JSON Multiple Loop Elements using tReplicate to tExtractJSONField

Anonymous — Wed, 27 May 2020 03:31:41 GMT

what are your expected result?
891;name1;value1
331;name2;value2
....
Is this your expected output? If so, as you did, use two tExtractJsonFields components for each loop element, one for result and another for attribute. In next subjob, add a new column for each data flow and set a sequence number for each row, this number will be the key for join on tMap.

Re: JSON Multiple Loop Elements using tReplicate to tExtractJSONField

Edith1 — Fri, 29 May 2020 19:48:25 GMT

Hello there,

As you suggested, I'm extracting the 1st & 2nd loop separately, then I'm adding the seq#. However, the sequece number is continuous and that's not what I was expecting. I want to know how to extract the child loop and what parent it belongs to.

Please see the attachment with screenshots of the API response, what data I need, how I've been extracting the data multiple ways and not getting nowhere.

Multi-Array API Response.doc.docx

Re: JSON Multiple Loop Elements using tReplicate to tExtractJSONField

tornilleiro — Sat, 30 May 2020 01:27:38 GMT

Hi edithmurillo,

What about put the two components tExtractJSONField in sequence. On the first tExtractJSONField you loop the result[*] without dig into attributes[*]. You pass the attributes[*] as a node for the following tExtractJSONField. In this second step e you can define other new loop over the attributes.

Regards

Re: JSON Multiple Loop Elements using tReplicate to tExtractJSONField

Edith1 — Tue, 30 Jun 2020 23:40:05 GMT

I figured out a way to get the result I needed using tHMap, now I need to figure out how to connect the tRestClient to the tHMap. Any suggestions?