JSON format to columar

[ViTLe]

Hi there,

I am very new to Knime and have been given a task to convert a JSON data format to columnar. I have read a lot of forums but cannot grasp my head around the logic. Where is a good starting point for me? or any examples someone can direct me to:

An example of the data is:

{“data”:{“Category1”:12344,"Category2:[“Green”,“Blue”,“Red”,“Yellow”],"Category3:[“House”,“Cat”,“Dog”]}

I’ve seen JSONPATH etc but still don’t have any idea where to start. At the present time, I have only managed to pull the data into a knime table from the database.

Any help is appreciated.

[elsamuel]

Welcome to the forum, @ViTLe.

What did you try with the JSON Path node?
Here I highlighted “12344” and clicked Add single query

For the other categories I highlighted one item and then clicked Add collection query:

The result is a table with an integer column and 2 collection columns:

The next steps depend on what you plan on doing with the data.

[ViTLe]

Hi Elsamuel,

Thank you! I am hoping to learn from KNIME a lot. I’ve only ever had experience with Excel and I am an advanced user but after meeting people who use KNIME frequently, they have said KNIME is much more powerful for analysis.

I was planning on adding the JSONPATH after reading the data from a table in my database using DB Query reader.

I will try your suggestion and let you know what happens.

Thank you.

[ViTLe]

Hi Elsamuel,

Thank you for your advice! I think it worked? I can see the categories (which are different to the sample I gave you but same structure) and I added the categories that I needed to extract into columns as there were multiple categories in the JSON data format that I did not need ATM.

I just need to work out how to extract the data into Excel format as I keep getting this error: Execute failed: Unsupported column type ‘List’. Please remove corresponding column(s) from the input table.

[elsamuel]

Like I said in my last post, the resulting table had some collection columns with lists, and the next steps depend on what you plan on doing with the data. In your post, you didn’t mention what this was.

Now you’ve said that you want to take this to Excel. Excel doesn’t have a corresponding format for lists, so that’s why you get that error. There are a number of ways to proceed, but you’ve still given no details about what it is that you’re trying to do.

Here are some options:

• If you want to preserve the current format of the lists, but as a simple string, then you can use the Collection to String node.
• If you want to split the lists into separate columns, then you can use the Split Collection Column node
• If you want split the lists into separate rows, then you can use the Ungroup node.

What exactly is the output you’re looking for?

[ViTLe]

Basically exactly what you showed in the first image with each category in a separate column is my goal, row data format into column data format and then export into Excel.

Thanks for your time and apologies for being vague in responses and not giving enough information as I was neck deep in trying to work this out and a bit stressed. It was not my intention to frustrate anyone or waste anyone’s time which is the vibe I get from your response.

I will look into the nodes you suggested for exporting into Excel as well.

Thanks again.

[Daniel_Weikert]

After the json path you most often use ungroup to put the list values into separate rows like @elsamuel pointed out with his very nice and detailed explanation.
br