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.