json data transformation.

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:

image

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

Thank you!

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

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!

4 Likes

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