0 ×

JSON to Table

JSON related functionality for KNIME version 3.7.1.v201901281201 by KNIME AG, Zurich, Switzerland

Converts a JSON column to multiple columns, whereby the column list is heuristically extracted from the JSON structure. It can either extract the primitive leaf elements (like strings and numbers), omitting the JSON tree path, or the full JSON structure. The latter, however, may yield some confusing output as the types of the columns are again JSON or collections of JSON.
Note that this node is meant to be used for "well-structured", relatively flat JSON objects which adhere the same schema in all rows. In case the JSON objects are more complex it's better to use nodes like JSON Path or JSON Path (Dictionary).
Some examples on the following JSON column input may help to clarify the generated output.

JSON
{"a": {"b": [1, 2], "c":"c"}}
{"a": {"b": [3], "d":null} }
Some options with their results: Only leaves, Use leaf name (uniquify with (#1)/(#2)/...)
Types:
  • b - JSON when Keep as JSON array, list of integers when Keep as collection elements
  • c - String
  • d - String
(The actual order of the columns might be different.)
bcd
[1,2]c?
[3]??
Only up to level 1, Use leaf name (uniquify with (#1)/(#2)/...)
Type: a - JSON
a
{"b": [1, 2], "c":"c"}
{"b": [3], "d":null}
Only leaves, Use path with separator ., Expand to columns
Type:
  • a.b.0, a.b.1 - integer
  • a.c, a.d - string
a.b.0a.b.1a.ca.d
12c?
3???
For nested objects, see the following example:
JSON
{"a":[{"b": 3}, 4]}
{"a":[1]}
Only up to level 1, Use leaf name (uniquify with (#1)/(#2)/...), Omit nested objects, Expand to columns:
Type:
  • a - list of integers
a
[4]
[1]
Only up to level 1 or 2, Use leaf name (uniquify with (#1)/(#2)/...), do not Omit nested objects, Expand to columns:
Type:
  • a - list of JSON values
a
[{"b": 3}, 4]
[1]
Please note that in the first row the value is a KNIME list of the two JSON values: {"b": 3} and 4, not a single JSON value, similarly in the second row, you get a KNIME list of a single JSON value: 1.
Though with Keep as JSON array (regardless of Omit nested objects):
Type:
  • a - JSON values
a
[{"b": 3}, 4]
[1]

Options

Input JSON column
Name of the JSON column to expand.
Remove source column
When checked, the input JSON column is removed.
Use path with separator
The output column name will be created from the JSONPaths found, separating the parts of the path with this value.
Use leaf name (uniquify with (#1)/(#2)/...)
The output column's name is the leaf's key, disambiguated by the numbers with (#nn).
Arrays|Keep as JSON array
The JSON arrays are not expanded, they kept as JSON arrays.
Arrays|Keep as collection elements
The JSON arrays are not expanded, but returned as KNIME collections.
Arrays|Expand to columns
The JSON arrays are expanded to columns, one for each value. (This might create a lot of columns.)
Only leaves
Only the leaves are returned, no intermediate values are extracted (as JSON columns).
Only up to level
The columns are generated only for paths with length up to this value (inclusive, starting from 1).
Omit nested objects
The nested objects are not included in the output when checked (except when the output column is a JSON column). This is sometimes desirable as sub-objects are extracted into separate levels. See also examples above.

Input Ports

Table containing JSON column.

Output Ports

Table with values extracted from the selected JSON column.

Best Friends (Incoming)

Best Friends (Outgoing)

Workflows

Installation

To use this node in KNIME, install JSON related functionality for KNIME from the following update site:

KNIME 3.7
Wait a sec! You want to explore and install nodes even faster? We highly recommend our NodePit for KNIME extension for your KNIME Analytics Platform.