Six Stars

How Can I read JSON/XML data from API and load into Mysql Table Using talend Open Studio for DI

could anyone please help in this :

I got the JSON file in this format and tried loading it into database

I have extracted file through tREST component and using tExtractJSONFields and could not apply logic in this component.

The JSON file is in this below attached format:

  

please help me in applying logic in textractjsonfields component.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Six Stars

Re: How Can I read JSON/XML data from API and load into Mysql Table Using talend Open Studio for DI

Thank you sir.

By applying your logic I can remove all strings.

I have done the job as

tREST--main--tExtractJSONFields--main--tJavaRow--tExtractDelimited--main-- tfileoutputdelimited 

I am getting all rows data in a single row but I want it to be in row by row like row1 under row2 under row3.

Could you please help in this.

Below are the rows:

rows[["jansi","13","100121521","female","7"],
["jay","14","989561914","male","5"],["janu","16","46566562","female","8"]]

I have attached my output.

 

Could you please help me in this.

Six Stars

Re: How Can I read JSON/XML data from API and load into Mysql Table Using talend Open Studio for DI

Could anyone please help me in this ???

3 REPLIES
Community Manager

Re: How Can I read JSON/XML data from API and load into Mysql Table Using talend Open Studio for DI

Hi
Read by JsonPath, and set the Loop jsonpath query to "$.rows[*]" to extract each row item such as ["jansi","13","100121521","female","7"], then, remove the characters "[","]", "\"" from the item string on tJavarow, for example:

//Code generated according to input schema and output schema
output_row.row_value = ((input_row.row_value.replaceAll("\\[","")).replaceAll("\\]","")).replaceAll("\"","");

Finally, extract each filed with tExtractDelimitedFileds, the job looks like:
tRest--main--tExtractJsonFiled--main--tJavaRow--tExtractDelimited--main--tLogRow

Regards
Shong

----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars

Re: How Can I read JSON/XML data from API and load into Mysql Table Using talend Open Studio for DI

Thank you sir.

By applying your logic I can remove all strings.

I have done the job as

tREST--main--tExtractJSONFields--main--tJavaRow--tExtractDelimited--main-- tfileoutputdelimited 

I am getting all rows data in a single row but I want it to be in row by row like row1 under row2 under row3.

Could you please help in this.

Below are the rows:

rows[["jansi","13","100121521","female","7"],
["jay","14","989561914","male","5"],["janu","16","46566562","female","8"]]

I have attached my output.

 

Could you please help me in this.

Six Stars

Re: How Can I read JSON/XML data from API and load into Mysql Table Using talend Open Studio for DI

Could anyone please help me in this ???