json string from mysql db and extract json fields

Anonymous — Wed, 31 Jan 2018 22:49:50 GMT

One column "Unknown" in local mysql db table has following 2 sample rows:

{"sourceData":{"1":"RPT_DT_ID","2":"APPL_SRCE_CD","3":"BASIC_CM_NO","4":"CHRG_LEND_ON_CHRG_CD"}}
{"sourceData":{"1":"20171231","2":"US","3":" 019044037984006","4":"Lend "}}

So essentially, each row is a json string.

I need to import from db and then extract the json values and map it to output columns in another table in local mysql db

I imported this column and wrote it to tfileoutputjson and I get something like this:

[{"file_record":"{\"sourceData\":{\"a\":\"RPT_DT_ID\",\"b\":\"APPL_SRCE_CD\",\"c\":\"BASIC_CM_NO\",\"d\":\"CHRG_LEND_ON_CHRG_CD\"}}"},
{"file_record":"{\"sourceData\":{\"a\":\"20171231\",\"b\":\"US\",\"c\":\" 019044037984006\",\"d\":\"Lend \"}}"}]

I need help in extracting this json data, store in csv and map to output fields

Re: json string from mysql db and extract json fields

Anonymous — Fri, 02 Feb 2018 09:21:41 GMT

Hi vshivkaushik

You can use tExtractJsonFields to extract data from the json string, you don't need to write the json string to a file, the job design looks like:

tMysqlInput--main--tExtractJsonFields--tMysqlOutput

please refer to the component documentation and learn this component, let me know if you have any issues.

Regards

Shong

topic Re: json string from mysql db and extract json fields in Talend Studio

json string from mysql db and extract json fields

Re: json string from mysql db and extract json fields