topic Re: Read Column Names and Extract JSON Data in Talend Studio

Read Column Names and Extract JSON Data

root — Wed, 21 Mar 2018 18:02:15 GMT

I have an JSON object:

{
	"RESULT_1": {
		"columns": ["col1", "col2", "col3", "col4"],
		"data": [
			["0", null, "12345", "other"],
			["1", "a", "54321", "MA"],
			["0", null, "76543", "RI"]
		]
	}
}

I am able to read the "data" part without issues using the tExtractJSONFields component.

CAPTURE_ONLY_DATA

However, I want to make sure that I read the column names from the "columns section" and make sure that the correct data is read and pushed in. For eg.

col1 should contain values 0,1,0

col2 should contain null, a, null

col3 should contain 12345, 54321, 76543

How can I achieve this? Any help is appreciated.

Re: Read Column Names and Extract JSON Data

Anonymous — Wed, 21 Mar 2018 21:44:36 GMT

I *think* I understand what you want and here is an example of how it can be achieved.....

The tFileInputJSON_1 retrieves the column names and is configured like this....

The tFileInputJSON_2 retrieves the data columns and is configured like this....

You don't need to use tFileInputJSON components for this, I just did as it made it quicker for me to try out. The logic will still work with the tExtractJSONField components.

Hope this helps.

Re: Read Column Names and Extract JSON Data

root — Thu, 22 Mar 2018 19:46:57 GMT

@rhall: Thank you. This makes sense and unite the rows/column together. But, how can I use the columns to make sure that the data is inserted correctly? I mean, tomorrow, if col3 comes before col1, (same for data also), then, I want to ensure that Talend is able to read the column names and save it correctly in a DB table.

Thanks.

Re: Read Column Names and Extract JSON Data

Anonymous — Thu, 22 Mar 2018 22:04:18 GMT

This problem sounds almost precisely like a problem I wrote a tutorial for a while ago. Take a look at this and see if it helps....

https://www.rilhia.com/tutorials/dynamic-column-order

Re: Read Column Names and Extract JSON Data

root — Fri, 23 Mar 2018 17:55:08 GMT

@rhall: Thanks for sharing your article. My challenge is that twice already, a couple of columns were added, and now, as you know I am referring to each element using the array placeholder.. "[0]". So, if a column is added/ removed, I need to reconfigure the whole tExtractJsonFields structure.

Re: Read Column Names and Extract JSON Data

Anonymous — Fri, 23 Mar 2018 19:24:32 GMT

Ah, I see. This is going to be tricky figuring out how you want to work with this data, but I can show you how to get the header data one header value at a time....

This will return your column headers one row at a time. You can get a count here and each header name.

To get the data you can do the following to get a "row". What this does is return the array structure one row at a time. So this essentially...

["0", null, "12345", "other"]
["1", "a", "54321", "MA"]
["0", null, "76543", "RI"]

You can use a bit of simple Java String manipulation to extract this data into columns.

Re: Read Column Names and Extract JSON Data

root — Tue, 27 Mar 2018 16:24:03 GMT

@rhall: Thanks. I think this may work...

Am i correct in assuming that this will be done in tMssqlRow, slightly slower than consuming data directly via tMssqlOutput component?

Thanks.

Re: Read Column Names and Extract JSON Data

Anonymous — Tue, 27 Mar 2018 17:06:20 GMT

If your data is needed inside a database then yes, you could use a tMSSqlRow to dynamically create your update statements. However, you could also use a dynamic schema if you have the Enterprise Edition. However, this will mean that you will need your database to have every column you may possibly receive in your data. Alternatively (if you want to get really dynamic) you could create dynamic DDL statements and add new columns when they appear in your JSON. In this case you would need to use the tMSSqlRow component as your DB schema would not be known.