Extracting specified columns and rows from JSON file

[sam502]

I have a JSON file as shown below:
{
“cols”: [{
“name”: “id”
}, {
“name”: “phase”
}, {
“name”: “ref”
}, {
“name”: “location”
}, {
“name”: “vendor”
}
],
“rows”: [{
“id”: “e21”,
“phase”: “1B”
}, {
“id”: “F56”,
“phase”: “2A”,
“ref”: “QWERTY”,
“vendor”: “IBM”
}
]
}

Ignoring “cols”, I am able to use a combination of JSON PATH, UNGROUP, and JSON TO TABLE to extract “rows” into a table with each row having the corresponding column headings .

How do extract “cols” in the JSON file in addition to “rows” into a table using “cols” as the table column headings? As shown in the JSON above, some “cols” do not always have values in “rows” that’s why I want to use “cols” for the table structure.

Any help greatly appreciated.

Thanks!

[temesgen-dadi]

Hi there,

Welcome to the KNIME community forum!

So if I understood you correctly you want to have the location column even if there is no data associated with it in the whole JSON file? Why?

Why a simple combination of JSON Path and Ungroup is not enough as shown in the example attached?

forum_json_path.knwf (8.4 KB)

Best,
Temesgen

[sam502]

Thanks for the response.

Yes, I want to have the location field even if there is no data. Why? I am building the workflow as a template to be used by various business units with different scenarios. Some business units might have all the fields and others might not. String manipulation is required for every field listed in “cols”. Thus trying to build one template to be used by all business units instead of having to build a workflow for each business unit. The fields listed in “cols” will be the same for all business units.

Thanks again.

[temesgen-dadi]

Hi Sam,

In that case you can extract the columns first and use the table structure via Concatenate node. See the example attached

forum_json_path_v2.knwf (20.6 KB)

Best,
Temesgen

[sam502]

Thanks. Exactly what I want to achieve. Greatly appreciated!