How to convert a nested JSON into a table

Hi community,
I’m struggling with converting a nested JSON to a table for hours now. I have already looked at many forum entries about it, but have not found the solution anywhere or it does not work for me.
Check the JSON and desired output below.
I tried it as I found in response of ArjenEX (JSON parse) with two JSON Path Nodes in a row and afterwards with an Ungroup Node:


First JSON Path:

Second JSON Path:

But I didn’t get the desired result:

This is the JSON input:
sample.json (2.6 KB)

This is the desired output:

I hope, somebody can help me with my issue.


I’m working with KNIME 5.2.5 Build June 04, 2024

Hi @theberha! One way to get the outcome is to convert the JSON to XML and use xpath to extract the data. Attached an example.
JSON EXAMPLE.knwf (80.8 KB)

1 Like

Hi @Data_consumer ,

this is indeed a working solution. Many thanks for it. But I’m wondering, if it isn’t possible to do the same without converting to XML.
Why can’t the Group Loop Start / End node be used directly with the JSON Path output?

Hi @theberha ,

Parsing JSON nesting from top to bottom。

This JSON file actually only has two layers of nesting (collect), So there’s no need for a loop.


JSON File Parsing.knwf (15.1 KB)


Hi @tomljh,

many thanks! This works!
I’m just wondering, I did it the same way, but my solution is not working. Shows missing values in the ids column. But with your solution, it is working fine. Need to compare the two solutions :slight_smile:

1 Like

Update: I found now the difference between my solution and your solution: When I’m configuring the first JSON Path Node by selecting the part I want and click on “Add collection query”, I get the path “$[‘history’][*][‘changes’][*]”, but your solution doesn’t have the last part (“[*]”) in the path. When I’m removing it, it works. Do you know the difference?




My understanding is: It has nothing to do with the quantity of the “*” sign, but they should be the same in number. For example: If there is one in the first item of the expression, then there can only be one in the second item.Otherwise, the data parsing will be misaligned.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.