From a multiple JSON field to single columns

Hi everyone,
I need your help because I have a table from a postgresql in which there is a json column:

For each parameter in this json field I need to obtain a column with the relative value, like this:
image

Not every rows has the same parameters inside the json field…I want to see, if there is no the parameter, NA.

Many thanks!!
Giad

Hi there @giad,

from your screenshot seems you have a JSON structure but not a JSON column. From String column this seems a bit complicated so I suggest transform your column into proper JSON column (String to JSON) and then try some JSON processing nodes to obtain your desired structure (also should be possible to get this column as JSON in KNIME from your database - check Output Type Mapping tab of database Connector node). Additionally if you share example data (doesn’t have to be real data, dummy that represents real well enough is good enough) someone might play with it and give suggestions/example workflow :wink:

Br,
Ivan

2 Likes

Thanks @ipazin!
I share with you an example of data:
example_data.xlsx (10.2 KB)

I want to obtain this output:
example_output.xlsx (10.6 KB)

if a parameter does not have the parameter i want to see NA.
Can you help me please?
I have transformed the column parsed_parameters in a json column on Knime, then I have used Json to table but it does not work…

Thanks
Giad

Hi there @giad,

considering you don’t have exact structure I would use JSON Path (Dictionary) node which is pretty handy if you know all possible parameters. In it you define column name and type plus it will return missing value (denoted in KNIME with red question mark) in case some parameter can not be found. Then, if needed, you can use Missing Value node to replace it with NA. Here is example workflow from you input data based on which you can continue…

2020_03_16_Playing_With_JSON.knwf (32.0 KB)

(Not a JSON expert so maybe there is simpler way)

Br,
Ivan

2 Likes

Hi @ipazin, it does not work with json path. I can do it in R Studio. Can I connect it after R studio to Knime?
for example, I make a dataset in R Studio with what I want, then is it possible to read in Knime this dataset from R Studio?

many thanks
Giad

Hi there @giad,

hmm… Why does is it not work with JSON path?

Of course you can do it with R as R integration is possible with KNIME.

Here is topic where @mlauber71 nicely summarized it:

And you have KNIME Hub for workflow examples:

https://hub.knime.com/search?q=R&type=Workflow

Br,
Ivan

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.