JSON transformation

I want to use a table for larger conversion of data to json format. Here is is how the json code should look like for my api to work.


“text” : [“My name is Max Mustermann”],
“source_lang” : “EN”,
“target_lang”: “DE”


However when I create it from the table to json node it looks like this. The square bracket is missing on the text tag. Is there a way to do this modification?

Hi @justmattam ,

As you probably realise, the square brackets in json signify that this is an array element, so the API is expecting to receive a list of one or more items as the text string. In your case it will be set to a “list” of one item.

There are a few ways to achieve this in KNIME. You first need to use a node that is capable of creating a List element. The nodes that immediately spring to mind that can create a list from a single cell or Cell Splitter, Group By and Column Expressions, and just as I was originally posting this, Column Aggregator also came up as a possibility.

Table to JSON, which I see you used, can then give the correct result provided it is suitably configured. Columns to JSON can also produce the required result in this case with less configuration.

Cell Splitter can create a List from a single item by specifying a delimiter that won’t appear in the text such as the ¬ character. Or if the text is all on one line you could specify \n as the delimiter.

Group By can create a List by using “List” as the aggregation method, but you will need to group it by something that is unique to the row, so you can use the Row ID node to create a key column containing Row ID. Alternatively you could use Counter Generation, or any node capable of creating a unique value per row. You also need to rejoin the grouped output to the original data set. As there should be a one-to-one match between rows I could have used Column Appender here, but I used a Joiner because it can also remove the additional (now no longer needed) columns that have been created.

Column Aggregator can aggregate a single column into a List. (Remember that you can tell it to output the new column as the required name “text”, and then you just need to reorder the columns.)

Column Expressions is arguably the simplest solution here in terms of node count, and requires just a very small piece of code (a single line) to create the array element. You must make sure you configure the Column Expressions node to return a “Collection”.


Here is a sample workflow
Create array for output in JSON.knwf (41.2 KB)


Thank you so much Takbb for sharing a detailed analysis and options on how to resolve it. Really helpful.

1 Like

You’re welcome @justmattam, and thank you for marking the solution.

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