JSON to Table - column names in separate array to data

[TommyBurn]

Hi,

I have a JSON in the following format, and i cannot workout how to load it into a table:

{
“example_data1” : {
“columns” : [ “id”, “name”, “age” ],
“records” : [ [1, “tom”, 40], [2, “jeff”, 29], [3, “bob”, 35] ]
}
}

Can anyone help please?

[ScottF]

Hi @TommyBurn and welcome to the forum.

I tried copy-pasting your text into a local file on my system, then used the JSON Reader to read it in. I had to change your quote characters to " instead of the “ and ” you have above to get it to work.

[TommyBurn]

@ScottF

e above to get it to work.

Hi Scott,

Thanks for the reply. I can read it in to KNIME (don’t know why the quotes went like that when I posted it), but i can’t work out how to convert the resultant JSON into a table (e.g. that i could load into a database)

[TommyBurn]

I did get something that “worked” in the end, but it’s very ugly! This cannot be the best way to do it. I expect i could write something in Python to handle it, or even SQL for that matter, but i was really hoping for a visual solution using KNIME nodes.

If anyone can provide a best practise alternative, i’d be really grateful. If the answer is to do this in Python, is there a fairly simple code - I’ve not had a chance to experiment because the extension is not working for me and i suspect i have to re-install. Thanks

[TommyBurn]

To explain the above flow:

I extract the relevant array (records not column names), then i pivot the records into columns using JSON to table, filter out the rest of the JSON, and then transpose the created columns into rows (1 row per record now). I use 3 string replaces to get rid of brackets and quotes in the data, then split the data on a comma delimiter, filter out any columns i don’t need, add the column names in and then send it to a SQL table.

Ugly, ugly, ugly, but i just wanted to see if it could be done. i am sure someone out there will, after they have finished being appalled at my solution, provide a much simpler approach. thanks

[Daniel_Weikert]

Hi I think scott was referring to using json path and ungroup as well
sth like

result like

br

[TommyBurn]

Yes!

Perfect!

Thank You!

The problem I had was that I couldn’t create the Collection Queries for the records. Once I created one for [Records][][] and then edited it, to change the * in the second square brackets to the column numbers, all was good!

Thanks so much, I get it now.

[Daniel_Weikert]

Glad that you fixed it. Thanks for the feedback and best regards