Does anybody have an idea about how to convert JSON-stat file to a table from a REST-API server?

[emre]

Hi there,

I am very new in Knime and Data Science. I searched a lot, but could not find a similar topic which could help me to solve my problem.

I am trying to GET data from a web page which supports REST API. There are two options for formatting; Json-stat and csv file. When it comes to csv I am able to get data and create a table. But json-stat is not flat and I couldnot write json-path or even do not understand how I can achieve that. I tried approximately every combination in Knime forums and hub. Json to table, Jsonpath, Cell splitt, ungroup and so on…

Here is an example data: https://data.ssb.no/api/v0/dataset/1120.json?lang=no
I want to get this data with GETRequest and drive that into a table which is available for further manipulation.

I hope there will be someone to help me to make this work. It is important for my further work.

[emre]

Is there anybody to help me on this? As I understand there should be one or more loop to make it work. But I have really no idea about how to do that.

[mlauber71]

You might have a look at this examples and see if you could define a path that would help you. The structure seems to be quite unique.

It might well be that you have to define sub-json paths. Extract a part of JSON and then define a new path like in the example.

kn_example_json_get.knwf (26.0 KB)

[emre]

Thank you for your answer. I got your point and it helps me a lot.

Do you have any idea about how one can convert it to a table? I mean JSON-stat files are files that contains table with a described pattern. Also they could store very large amount of data. So I am afraid of that I could change data when I am trying to get it. For example, we have 2 color options, 3 material options and 6 values. How can I extract this table without interference?

I kind of create a workflow which operates Unpivoting many times as “id” arrays length. But I am not sure if it could work with all JSON-stat files.

I share the file with my entry, so you could check if I am wrong.

JSON-stat.knar.knwf (92.8 KB)

[Andrew_Steel]

Hi @emre,

this is a more generic solution.

It seems, that the outcome of your and my solution are the same. I saved your outcome into a csv File and matched your data with my .

I started with your GET Request. Based on the JSON-stat description I get the list of the used id[] with the JSON Path Node.

WARN: There is a different use of this ID in your dataset: JSON-stat Format says that the parent from ID is ‘Root’ (when class “dataset”). In your dataset the parent is ‘Dimension’. My solution based on your dataset. The JSON Path Node must be changed, if anybody used the right format.

Based on this ID list (so you can change this list or the count of the id[] in your dataset) I collected all values of the id’s. The collected size is 21 * 3 * 174 *1 (Alder * Kjonn * Tid * ContentsCode). At last the table were appended with the extracted values from value[].

I could not assign some of the data of your outcome correctly, so I left them out of my solution.

I hope I could help.
Andrew

JSON-stat-generic.knwf (38.8 KB)

[emre]

Hi Andrew,

It seems very professional. That is what I was thinking of. I didn´t have enough knowledge to make this so clear.

Thank you for your time.
Emre