Json to Table

[Nagaraja_Ganiga]

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.

 

[roodrood]

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? 

 

[glenerik]

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
	}
]

 

[mlauber71]

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.knar

[sshiell]

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

[ScottF]

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

[siri]

Hi Scott,
I was searching to understand how to generate a table from an XML data file and came across this post. Is there a way/node in Knime to automatically detect the XML code and generate a data table? I tried the converting to Json and then JSON to Table nodes method and it is not working. probably my data is too big to handle (Increased the memory space, but still showing java heap memory issue). My data has around 120 columns and they vary each time depending on the url I use to pull the report. So, defining them each time isn’t time efficient. Any suggestions to automatically detect the column names based on the XML tags? Any help is appreciated.

Thank you.

[ScottF]

Instead of converting to JSON, have you tried using the XPath nodes? There are several example workflows on our Hub demonstrating their use:

https://hub.knime.com/search?q=Xpath&type=Workflow

There are also some threads here on the forum where folks have implemented XPath in their workflows.

[siri]

Yes. I used XPath and it is retrieving the data without any issues if I define the attribute paths (uploaded the screenshot of the Xpath. With more than 100 attributes in each file, I have to define the Xpath for each variable manually. I am wondering whether there is a way to fasten the process and make it more time efficient? Some process or Node that can detect the main XML attributes in the file automatically and converts into table format. Does that makes sense?

Thank you.

[ScottF]

Someone with more XPath experience than I have might be able to chime in here, but my impression is that this type of automated detection will be difficult until KNIME supports XPath 2.0 (see here: Creating column names with XPATH Node). A ticket has been filed about that issue, but I don’t have an ETA on when the XPath nodes might receive an upgrade.

[siri]

Thank you for the clarification Scott.

[siri]

Hi Scott,
I am trying to automatically convert the json format data into table using ‘Json to table’ node. I don’t want to use the JSON Path node as I have to manually define the paths. ‘Json to table’ node is doing a very good job in identifying the attributes and retrieving the data. But, the issue is, it is pulling everything in a single row as shown below by duplicating the attribute names for each entry/row. What can be the best practice to put the data shown in the screenshot into two rows and 4 columns? Thank you.

[ipazin]

Hi there!

I have played around with it and build a workflow example providing both solution when you have defined number of columns and unknown number of columns. Sort of

Take a look and if any questions feel free to ask.
2019_05_16_Concatenating_Columns.knwf (44.1 KB)

I guess it would be better if you can get the format you need directly from JSON to Table node…

Br,
Ivan

[Andrew_Steel]

Hi siri,

it looks like an array. So you have to split this array in single rows.
Use a JSON Path with ‘$[ * ]’ as JSONPath.

You must then ungroup this array into single JSON Rows. And at the end the ‘JSON To Table’ node to extract the data.

Andrew

[siri]

Thank you Andrew. Appreciate your quick response. The concept is working and I am able to generate a table with multiple rows from Json code with the syntax, only if I remove the additional prefix text in the JSON code I pulled from the URL. That is ‘{Report Entry:’ at the starting of the code and ‘}’ at the end of the json code need to be removed. The additional text is messing the format. I removed it manually and tried to test the flow and it worked. I am trying to modify the code in Knime using JSON transformer. But, I am not sure whether there is an easy way to select the text from ‘[’ to ‘]’ (including the square brackets) in the code?
{ Report Entry: [ {
“firstName” : “Test”,
“lastName” : “D”,
“Company” : “XYZ, Inc.”,
“Employee_ID” : “012234560”
}, {
“firstName” : “Amy”,
“lastName” : “Duke”,
“Company” : “XYZ, Inc.”,
“Employee_ID” : “00123467”
}
]
}
Thanks,
Siri

[Andrew_Steel]

Hi siri,

sorry for my late response. I hope the Report Entry stands in quotation marks
Then change the JSONPath into $['Report Entry'][*] and it works fine.
If you read your JSON Data from JSON file this entry in JSON Reader node works also. In this case you need no Ungroup node

Andrew

[siri]

No problem at all Andrew. Appreciate your response on this. You are correct about the quotation marks. I slightly modified the syntax in order to capture the data. $[‘Report Entry’].[*]
That’s probably because of the way the JSON code is structured.
Thanks a lot for your help.
Siri

[HairyDrumroll]

You can do JSON to Table, then Transpose, then JSON to Table again. This should work.
Thanks to @moritz.heine on this post for this idea!

[denisfi]

Hi Guys and Ladies!

I saw all the information posted here. I’m using knime 4.6 and could solve my problem with part of the solution here.

First i used the json path to map the information, then use Ungroup to split in rows the array in multiples rows.

On my example, I’m trying to get some information about dns server with multiples domains. The response on json were huge… so i used this process.

I made a rest request, then filtered by a field that I know (dmarc). after that, I mapped the response with path like that with “Json Path”:

The array were set as list group, other fields as string or number… as you wish…

after that, used “Ungroup”, selecting the group fields:

That were the result:

It work´s very fine to me!!!

Thanks all for the suggestions… I hope this works fine to you too!!