Table to JSON

Converts the selected columns content to a JSON value row-wise or column-wise. It also have an option to "undo" -with some limitations- the JSON to Table transformation and create JSON values for each row based on the column names.
Example input table:

a.b	a.c	d
b0	c0	d0
b1	c1	d1

With the different parameters, the following JSON values are generated:
Row-oriented

[ {
  "a.b" : "b0",
  "a.c" : "c0",
  "d" : "d0"
}, {
  "a.b" : "b1",
  "a.c" : "c1",
  "d" : "d1"
} ]

Column-oriented (with Row keys as JSON value with key: "ROWID"):

{
  "ROWID" : [ "Row0", "Row1" ],
  "a.b" : [ "b0", "b1" ],
  "a.c" : [ "c0", "c1" ],
  "d" : [ "d0", "d1" ]
}

Keep rows (with Column names as paths, separator: .):

{
  "a" : {
    "b" : "b0",
    "c" : "c0"
  },
  "d" : "d0"
}

{
  "a" : {
    "b" : "b1",
    "c" : "c1"
  },
  "d" : "d1"
}

Options

Input columns: The selected columns will be transformed.
Row keys|Omit row key: The row keys will be omitted, not used in the generated JSON value (which are arrays when the rows are not kept).
Row keys|Row key as JSON key: The row keys are added to the generated JSON value as a key, in this case not an array, but an object is created.
Row keys|Row key as JSON value with key: The row keys will be included in the generated JSON (array) value with the specified key.
Row-oriented: the values from the selected columns are collected and concatenated row-wise to a JSON object/array, after that these are combined by the rows to a single JSON value.
Column-oriented: the values from the selected columns are collected and concatenated column-wise to a JSON object/array, after that these are combined by the columns to a single JSON value.
Keep rows: the selected columns are combined to a new JSON column containing the values from the columns and the name of the columns as keys. This option does not combine the rows of the input table.
Remove source columns: When checked, the source columns are removed when the rows are kept. It has no effect for the other Aggregation direction options, when all columns are removed.
Column names as paths, where path separator in column names: When From column names as path is selected, this value will be used to find the keys for JSON columns based on the column names.
Missing values are omitted: Missing values from the input table do not generate a key in the resulting JSON structure, they are omitted completely.
Note that in a column-oriented transformation (see above) missing cells will still be inserted a null values in the column's array because otherwise the row arrays for different columns may have different numbers of entries. This would make it impossible to reconstruct the original table
Missing values are inserted as 'null': Missing values from the input table are insert as null values.
Output column name: Name of the resulting column.

Input Ports

: A datatable

Output Ports

: Table containing the JSON column

Popular Predecessors

Popular Successors

Views

This node has no views

Workflows

Developers

You want to see the source code for this node? Click the following button and we’ll use our super-powers to find it for you.

Installation

To use this node in KNIME, install the extension KNIME JSON-Processing from the below update site following our NodePit Product and Node Installation Guide:

v5.3

A zipped version of the software site can be downloaded here.

Plugin provider: KNIME AG, Zurich, Switzerland

Plugin version: 5.3.0.v202406280759

On NodePit since: 2024-07-09

Last update: 2024-11-21

KNIME versions: Since v3.6

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!