Extract ColumnName and Value from JSON

Six Stars

Extract ColumnName and Value from JSON



I have a requirement which I am not able to figure the solution for it myself.

Requirement : There is a product table which has following columns

Prod_ID - contains product ID

Prod_Name - Contains product name

Prod_config - Contains additional information about the product features in JSON format (as a sting)

Each product can have one or many features, the information of which is present in Prod_Config in key:value pair in JSON structure. I want to transform this JSON text into table format 


so the the value of Prod_config 



should be transformed and put in separate table like this 



I linked my database input to tExtractJSONFields




From here I can manually configure individual attributes and write appropriate JSON query to extract information from them. But the problem is, the table data is from external source which I have no control on + are you might have noticed in the example one product can have 1 attribute another can have 50 and another can have 100+. I need a dynamic know all the attributes present and transform them into the require table format without manually configuring (something that I have no control of and can change very frequently). There can be new attributes added to products so I should not have to come back and update my JOB each time that happens. Hope I have explained my problem and restriction for solution.


Any help would be highly appreciated. Thank You in Advance.




Thirteen Stars

Re: Extract ColumnName and Value from JSON


as a variant - use search function :-)

(it more than 1 of course)



Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.