looping complex JSON input

[nivedhitha]

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.

[Anonymous]

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

[nivedhitha]

@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

[nivedhitha]

@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