json data transformation.

[kotlapullareddy]

Hello All,

I have JSON data in the following format, which includes both columns and rows.

JSON Data:

{
    "column": {
        "id": "dropdown",
        "title": "Product"
    },
    "id": "dropdown",
    "text": "Financial Reporting"
}
{
    "column": {
        "id": "status",
        "title": "Status"
    },
    "id": "status",
    "text": "Not Started"
}

I can extract data for columns (['column']['title'] = Product & Status) and rows (['column']['text'] = Financial Reporting & Not Started) separately. However, my final output should look like the table below:

I have tried pivoting but with no success. Can someone please help me convert this into a table in KNIME?

Thank you!

[Daniel_Weikert]

Hi
Looks like first using json path node for extraction and then do a transpose operation (table transposer). Have a look at those nodes
br

[ArjenEX]

Hi @kotlapullareddy

You can go different directions here. The transpose one as mentioned by Daniel is a good one. I would use;

• Collection query on the JSON.

• Transpose the output and split the collection. Due to the order of the JSONPath the header should be the first row.

• Then split the table based on row number = 1 to separate the headers.
  

  

• Convert the values of the header to a flow variable and pass this along to a column renamer node.

• Finally apply the flow variables to the newName of column0 and column1.

Hope this helps!