Creating columns from JSON

[Unlockedata]

Hi,

I’m hoping you can help me! I’m new to JSON and I’m new to Knime.

I’m working with a JSON file structured like this:

I want to be able to write to a table where the “@k” values are column headers and the “@v” values are populated under the appropriate column heading. Looking at the image above, I want “addr:city” to be a heading and “Derby” to be a value underneath that heading.

I’ve created a JSON Path that gets me:

.

After Ungrouping and using JSON to Table, I get the following, which still isn’t right.

Does anyone have any advice that can get me back on course?

Thanks in advance.

Cheers - John.

[ipazin]

Hello @Unlockedata,

and welcome to KNIME Community!

From experience sharing data (dummy works just fine) with community helps as one doens’t have to simulate/guess your JSON structure

Br,
Ivan

[Unlockedata]

Hi Ivan,

See attached JSON test data.

TestData.json (5.3 KB)

Thank you for taking the time to look at this - much appreciated.

Cheers - John.

[ipazin]

Hello John!

Does this flow makes sense?
2021_01_14_JSON_Manipulation.knwf (20.2 KB)

Br,
Ivan

[Unlockedata]

Ivan - wow! Thank you sir, I appreciate this. Really!

A final question, if you don’t mind? How do I turn the data in to multiple rows of data?

Each “Node” and “Way” has an “ID” that is unique to that ‘container’ - how can I have all the values within each container in a row?

I hope the attached explains what I’m going on about!

Example output.xlsx (9.9 KB)

You’ve done a lot for me here - thanks.

All the best,

John.

[Daniel_Weikert]

Could your post be pinned so that everybody can see it immediately when access the forum?

[Unlockedata]

Hi Ivan,

The workflow works well and I’ve adapted it to be able to cater for far larger JSON files - I’m filtering by hotels. However, the output is still one row, when I’m trying to get a row per property (or @ID).

I’ve tried various Knime tools unsuccessfully. Do you have any pointers or recommendations?

Thanks again.

John.

[ipazin]

Hello John,

if I got it right here is modified workflow that outputs data in format from Excel.
2021_01_14_JSON_Manipulation.knwf (33.9 KB)

@Daniel_Weikert post is pinned in case marked as solution. (if that is what you had on mind)

Br,
Ivan

[ipazin]

Hi @Daniel_Weikert,

I see now you meant on post about sharing data. Well there is this topic I try to point users to:

Reproducible (Minimal) Workflow Example

Hi there KNIMErs! What is this topic about? This topic is about helping yourself and KNIME community! How? By creating and sharing workflow examples that help and can be successfully ran by others When? When logic requires it. For example when you are opening a topic in order to get help (you are getting error or can’t configure node and would like someone else to take a look what is wrong; or you started a workflow and got stuck so need help with next step), when you are reporting …

Probably it’s worth writing it down somehow somewhere but not sure main forum page is the right place.

Br,
Ivan

[Unlockedata]

Hey Ivan,

Your workflow has done practically everything, thank you. The one final area I’m trying to resolve is grouping all the attributes in to the right row, for data accuracy purposes.

If I look at the source data, the Premier Inn data does not include a postcode.

In your final ungroup, you will see that there’s an addr:postcode for Premier Inn.

The addr:postcode value has been populated from another property in the source data:

This pattern is consistent and I’m trying to work back through your workflow to understand where that’s happening. Playing with the group and ungroup, but, if you have any quick ideas on finalising this, I would really appreciate it. I’ll keep plugging away.

Cheers - John.

[Unlockedata]

The gift that keeps on giving Ivan.

I can’t break the back of my problem here; looking at the data and the workflow, the problem I have is that the relationship between the key value pairs and the parent object is lost and I can’t work out how to re-establish it, so I think I need to take the “ID” and other objects through the entire workflow so that I can ensure data integrity and accuracy in the final output.

One approach I’ve looked at is below and I’d appreciate your view - per row analysis, in a loop?

In my latest version (below) I’ve searched the data for hotel data (this is what I’m interested in) and then put that data in to a table. In order to maintain the relationship through out, do you think a loop that iterates in to your workflow is viable?

I tried taking the ID (circled in red) with the data, but the accuracy is lost because of the problem is mentioned last week.

Appreciate your input here, if you have the time. And will.

Cheers - John.

[ipazin]

Hello @Unlockedata,

I see. Take a look at this workflow:
2021_01_14_JSON_Manipulation_Mod.knwf (57.5 KB)

Probably this can be done somehow without using loop but might be an overkill.

Br,
Ivan

[Matt_D]

Not sure if it’s my place to add to this considering that it’s already been solved but I love parsing JSON

Here was my solution, hopefully it’s what was required, if not ignore me!

[ipazin]

Hello @Matt_D,

better solution are always welcome, especially loopless ones Share workflow with us?

Br,
Ivan

[Matt_D]

Here you go, I didn’t keep it so quickly rebuilt this morning.

2021_01_14_JSON_Manipulation.knwf (17.9 KB)

[Unlockedata]

@Matt_D and @ipazin - guys, please excuse my delay in responding - I’ve used both and they worked so well and I cracked on with creating my solution that I forgot to thank you.

Guys, thank you both - great work, patience and fantastic support.

All the best,

John.