json string from mysql db and extract json fields

[Anonymous]

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

[Anonymous]

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