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.
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:
json_split.knwf (16.4 KB)
The result should look more like this:
Hi there @gentile,
welcome to KNIME Community!
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
json_split_ipazin.knwf (35.5 KB)
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
you are welcome. I see. So you need to develop additional logic. Not so much familiar with JSON so maybe someone else will have an idea. Good luck!
Thank you, @ipazin.
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):
What I want to achieve is this:
The rows with colour are the ones that need to be repeated with every child.
Any help is highly appreciated! Thanks.
PS: I thought “column expressions” and “math formula” nodes sound interesting. But they look pretty intimidating
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 )
you are welcome but don’t think it will go without scripting (or it will be overkill) but maybe someone has a better idea.
Good luck again
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?
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.