Creating columns from JSON

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:

image

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.

image

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

Thanks in advance.

Cheers - John.

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 :wink:

Br,
Ivan

1 Like

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.

Hello John!

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

Br,
Ivan

2 Likes

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.

2 Likes

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

1 Like

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.

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

Hi @Daniel_Weikert,

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

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

Br,
Ivan

1 Like

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:

image

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.