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 ,

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

[nivedhitha]

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

[Anonymous]

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

[nivedhitha]

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?

[David_Beaty]

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.

[nivedhitha]

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?

[lennelei]

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

[nivedhitha]

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?

[Anonymous]

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