Optimal way to convert Table to JSON

KNIME Analytics Platform
1

Hi there,

I need to convert a Table to a JSON file, for instance:

2018-06-20%2016_33_50-Group%20table%20-%202_30%20-%20GroupBy%20(Filter_%20maximun)

To this JSON file:

{
    "file": {
        "checksum": "blablabla"
    },
    "data": [
        {
            "parameter": {"qCCV":"blablabla"},
            "values": [
                {
                    "contextSource": "EAC(Carbamidomethyl)FAVEGPK",
                    "value": "222483000"
                },
                {
                    "contextSource": "HLVDEPQNLIK",
                    "value": "40148300"
                }
            ]
        }
    ]
}

I’ve seen that there’re several ways tro do it but I don’t know what’s the optimal approach. Any idea?

Thank you very much.

Roger

2

Hi @rolivella,

To create such a complex JSON structure I would recommend the following steps:

  • Use Group Loop start node to iterate over categories

  • Use the JSON to Table node to create JSON objects, excluding the category column using the Row-oriented aggregation direction option

  • Use the Constant Value Column and the current category value passed with a flow variable to create a cell with the category name

  • Collect the results with the Loop End node

  • Finally combine the created objects using the JSON Row Combiner node with the Collect into object with key option and selecting the corresponding category column.

This is the basic algorithm which you can adjust to your needs. Also please find attached a sample workflow.

Best,
Anna

CreateComplexJSONObject.knar.knwf (410.8 KB)

3

It works! Thank you for your help.

Roger