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

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


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 ???


All 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 ???

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

An API-First Approach to Modernizing Applications

Learn how to use an API-First Approach to Modernize your Applications

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog