Json to Table


#1

Hi Everyone,

 

Can anybody please tell me how to convert a Json file to a proper table with same filed names are unpivoted and in different rows (Converting multidimensional array to two dimensional) rather than having same field names as a duplicate and in same row.

 

Please let me know if my problem is not understood or need more details.

Thanks.

 


#2

Hi,

Your question lacks a bit of context, but I think I had something similar when trying out the JSON to Table node. 

Instead of the Json to Table, have you tried the JSON to XML node? In there, you can define which type of data you need to retrieve from your JSON. 

Could you please try it and keep me updated on this matter please? 

 


#3

Thi issue makes the JSON to table node almost pointless. It's not a table if it has 1 row. I have tried so many settings in the node and couldnt get it to give me a table with a basic JSON input of 5 fields. it converts everything to columns instead.

Here is an example of the data structure, I want this to yield 3 rows:

[
	{
		"date":1439006400,
		"amount":50
	},
	{
		"date":1439035200,
		"amount":51
	},
	{
		"date":1439049600,
		"amount":49
	}
]

 


to read excel files and register in csv format? How to do
#4

I just stumbled across this entry and maybe I can help since I had a similar problem recently. The point is that in order to import a JSON file you have to

  1. Import it with the JSON reader
  2. define a JSON path to the variables
  3. ungroup the variables

Then you have a normal table with columns.


JSON parsing for order data
#5

I looked long and hard for a solution and this worked. Can’t believe there isn’t more in the forums on it.


#6

Thanks for this example workflow - it concisely demonstrates the concept of reading in and ungrouping JSON to use in KNIME.

For others that may stumble across this in the future, I’ll also add a link to one of our blog posts from 2017 that touches on the same idea: https://www.knime.com/blog/will-they-blend-experiments-in-data-tool-blending-today-xml-meets-json