I’m new to KNIME, but I already love it. And the fact that there is such a large and lively community out there discussing and helping each other, is great.
I have gone through many Youtube videos and forum posts and have come quite a long way, but I’m currently stuck.
So, I’m looking for some help and hope you guys can share ideas and thoughts.
My challenge:
I have a bunch of JSON files that contain the result of a data analysis tool. This JSON contains nested arrays. I would like to be able to write the values into a table depending on their level of nesting (sort of).
I have been playing around with JSON Path and Unpivoting and so far I do get a table, but not in the way that I need:
I have taken a look at your workflow and this definitely seems like a task for Pivoting node. The only information missing for it is your group column. What it will actually define is what values goes into which row to get the output you are looking for.
I have modified your workflow and provided two solutions how to manipulate your data into somewhat similar format you are looking for. If it is not what you were looking for maybe it will give you some ideas
Thank you @ipazin for taking the time to look into that!
The result is not exactly what I was looking for, as the nested arrays should be in line with their respective parent. Plus each parent should be copied for each of its children.
But it definitely gives me ideas to look further into. Let’s see where this leads me to
I have played around further but I don’t get to the result that I need to achieve.
And maybe this is not necessarily a JSON problem. After all, I have a table
I “just” need a few nodes that I can tell which column represent what level and be able to reorganise the according values depending on these levels.
I learned that I must have unique column names and that KNIME will do that for me no matter how hard I try to keep them and make them available for further steps.
As I want to organise values with the same column names under another, by keeping the order they came in, I believe that not having the same names makes this complicated (at least for me).
I have simplified my input structure to make it more manageable. json_to_table.knwf (9.9 KB)
And this is the structure I get from my latest attempt (copied it to Excel in order to be able to show the levels of parent and children):
seems to me you should try with some scripting (Java, Python or JavaScript - Column Expressions node) to assign each row with a level. I did this assignment in workflow attached in my first reply using group loop and counter generation node but this is not correct obviously. After assignment use Pivoting node as I did followed by a Missing Value node with option Previous row to fill values and get your desired output format
Check this workflow to see how to proceed after you get your level calculated. json_split_ipazin2.knwf (39.5 KB)
OMG! Thank you, @ipazin! That’s exactly what I needed!
Now, I only need to find a way to create those levels based on the IDs (hopefully with some node and without needing to code )
Thanks @ipazin. I see what you mean.
For a moment I thought I could simply add the same level information to the same IDs, but that’s not the case. Plus, as my data is not always that small and clear, I will need a more generic approach.
Anyone with other ideas, who can spare me to learn to code during the holidays?
