topic Re: looping complex JSON input in Talend Studio

looping complex JSON input

nivedhitha — Mon, 08 Jul 2019 19:17:19 GMT

Hi everyone,

I'm having difficulty with a JSON file that I have to parse and load into mysql. screenshot below shows the beginning of the JSON data

to start with I'm just trying to loop and get all the _id and accountId. But stuck right here.

Im using a tFileInputJSON and the configuration is like below

I see that there are 9 records which means it is looping through all the objects but I don't get the right values. Everything is null here.

Can someone help pls?

Also attaching the complete JSON file .If you could also give me suggestions on parsing the rest of the fields as well, it would really be helpful.

Re: looping complex JSON input

Anonymous — Mon, 08 Jul 2019 21:27:55 GMT

Hi @nivedhitha ,

Your input structure contains multiple loops at different levels.

You can try to parse the file using Talend Data mapper, which will be easier.

Thanks and Regards,

Subhadip

Re: looping complex JSON input

nivedhitha — Mon, 08 Jul 2019 21:30:30 GMT

@subhadip13 ,

thanks. Will look into it.

Do you have any idea why im not able to parse the id and accountId objects?
they are really simple but I just get null values.

Re: looping complex JSON input

Anonymous — Mon, 08 Jul 2019 22:22:28 GMT

Hi @nivedhitha ,

As per your document I see it has multiple JSON elements.

hence you can't parse that with tFileInputJSON, you have to use tExtractJSONFields and group on id.

Thanks and Regards,

Subhadip

Re: looping complex JSON input

nivedhitha — Tue, 09 Jul 2019 11:28:34 GMT

Hi @subhadip13 ,

can you please elaborate your suggestion?

I have the JSON data as a file, so im thinking there has to be a tFileInputJSON at the beginning. how should this component be configured?

how do I take the input to tExtractJSONFields in the downstream?

Re: looping complex JSON input

David_Beaty — Tue, 09 Jul 2019 15:44:36 GMT

Hi,

I think part of your problem is that the JSON Query path doesn't like the NumberInt(6) type statements (line 29 example). So can't decode the values you need.

You might need to parse each row and convert those to just the integer value.

Re: looping complex JSON input

nivedhitha — Tue, 09 Jul 2019 17:04:49 GMT

Hi @dsoulalioux ,

thanks for your suggestion.

I manually removed all NumberInt and replaced it by just the integers to see if I can parse it properly, but I still get null for all id and accountid

above screenshot shows NumberInt removed in the source

tFileInputJson and the result below

would you suggest anything else for my situation?

Re: looping complex JSON input

lennelei — Tue, 09 Jul 2019 18:27:29 GMT

Hi,

you have to use jsonpath wihout loop in the "read by" field : you have no loop at root level.
You'll then have one line with the correct _id and annountId.
However, if you then need to loop over other values, you'll have to parse data one level after another and rebuild the output structure at the end.
I made a reply to a similar question a few days ago (the problem was not exactly the same but I can be resolved the same way):
https://community.talend.com/t5/Design-and-Development/tFileInputJSON-access-iterating-key-value/m-p/165530#M100609
Regards.

Re: looping complex JSON input

nivedhitha — Tue, 09 Jul 2019 18:52:35 GMT

Hi @lennelei ,

Thanks for your response. I will take a look.

now i'm not even sure if my json data is properly structured. I used an online JSON validator and checked my json file but it was invalid.

I had to make the below changes

1. replaced NumberInt(6) by just numbers

2. replaced ISODate("somedate") to just "somedate"

3. there are 9 different iterations in my JSON that are defined like {} {} {} {} {} {} which I had to change to [{},{},{},{}] (have added commas inbetween and [] added )

only after these changes, my JSON is even valid and im able to create a metadata for the file and also get all the IDs in a loop with the same configuration in tFileInputJSON as shown above.

Does it mean my JSON is bad? or should I get the data and make the above changes dynamically in my talend job?

Re: looping complex JSON input

Anonymous — Tue, 09 Jul 2019 19:36:15 GMT

Hi @nivedhitha ,

You can use the following job flow to get all the data for your complex JSON.

The First TfileInputFullRow is your InputJSON file and the second tFileInputFullRow is the converted JSON file to remove the pretty prints.

in tMap :

Use the above expression to get the JSON in non pretty printed way to the data mapper.

In Data Mapper create the I/P, O/P structures and the map out of it as below.

And after the map is created, the following file is generated with all your info: This is a comma separated file.

Please let us know if it suffices the requirement.

Thanks and Regards,

Subhadip

Re: looping complex JSON input

Anonymous — Tue, 09 Jul 2019 19:55:22 GMT

Hi @nivedhitha ,

In addition to this the ROW_SEPARATOR for tFileInputFullRow_2 should be "}\n" and that of tFileInputFullRow_1 should be "\n"

The ROW_SEPARATOR for tFileOutputDelimited_1 will be "" (component name as per images)

Thanks and regards,

Subhadip

Re: looping complex JSON input

nivedhitha — Wed, 10 Jul 2019 04:03:45 GMT

~~@subhadip13 ,~~

~~thanks for taking the time to work this out.~~

~~I still don't understand how to get the comma separated values(has just the values separated by comma ) to table.~~

~~like for example, any array with multiple object has to be written in multiple lines like the below example~~

~~please can you explain how the final csv can be loaded as shown in the above table.~~

~~the different sections of the file would go into different tables, but im now trying to understand the logic~~

posting an updated reply below

Re: looping complex JSON input

nivedhitha — Thu, 11 Jul 2019 17:34:08 GMT

@subhadip13 ,

I tried to work your suggestion out and have a few questions

1. I have been able to get the csv but also getting some errors and the values from JSON are a little off.

In the above screenshots, the JSON has the UnitB field null , but in the csv the value for UnitB is ProjectX which is the next non null value. Can you suggest how to bring in the null values as well so that I have the right value under the right key?

2. And when I enable output Delimited Header in the output structure of the map, then I get the header for each record in the csv as shown above and also the below error in talend. But this goes off if header is disabled. Can you suggest how to get the header once and get the corresponding values under it.

3. I created the input structure importing the JSON file itself and the structure is just as you showed. but we are actually creating a csv file before the tHmap step and the csv is what is being sent to the tHMap as input. So im now confused if the input structure has to be created importing the JSON file itself or the csv file that is being created in the previous step.

4. Also if you could tell me how to extract the different objects of array as multiple rows instead of columns, it would be really great. I can apply whichever way is needed

Thanks in advance