Icon

JSON_​Path_​extract_​optional_​values

Extracting optional values from JSON array of nested JSON objects
Extracting optional values from JSON array of nested JSON objectsSometimes you want to extract values from a JSON array and transform them into a KNIME table such thatone row corresponds to a single value of the array. This can be achieved using the JSON Path andUngroup nodes in combination like so: If the JSON array is nested, i.e., if it contains JSON objects or arrays itself, you sometimes do not want toextract its full values, but only parts thereof. For instance, if the values of the array are nested JSON objects,you might want to extract only certain values in the leafs of these objects: At this time, the preferred solution in KNIME is to insert an additional JSON Path node. Specifically, we usea first JSON Path node to extract the full values, i.e., the nested JSON objects, from the outer JSON array.Next, we ungroup the resulting collection of nested JSON objects, which results in a KNIME table in whicheach row contains a single nested JSON object. Finally, we use another JSON Path node to extract thevalues which we are actually interested in and which lie in the leafs of these nested objects: If values in the leafs of nested objects are optional and not always present, the JSON Path node emitsmissing values when values are absent. In this example, this is the case for leafs named optionalLeafInMandatoryInnerNode. This is a feature of the Jayway JSONPath library which the JSON Pathnode uses.Unfortunately, the Jayway JSONPath library does not yet provide similar functionality if an inner node of thenested JSON object is optional. In this example, this is the case for the inner node namedoptionalInnerNode which has a leaf named mandatoryLeafInOptionalInnerNode. This missingfunctionality leads to absent missing values in the output of the JSON Path node and, thus, faultyassignments of extracted leaf values to rows in the Ungroup node: extract valuesfrom nested JSON arrayone row per itemextract all valuesin leafs of nested objectsfrom nested JSON arrayextract all valuesin leafs of nested objectsone row per valuein nested JSON array;output faulty!one row per valuein nested JSON arrayextract selected valuesin leafs of nested objectsfrom nested JSON arrayone row per valuein JSON arrayextract valuesfrom JSON array JSON Path JSON Reader Ungroup JSON Path JSON Reader JSON Path Ungroup Ungroup JSON Path JSON Reader JSON Reader Ungroup JSON Path Extracting optional values from JSON array of nested JSON objectsSometimes you want to extract values from a JSON array and transform them into a KNIME table such thatone row corresponds to a single value of the array. This can be achieved using the JSON Path andUngroup nodes in combination like so: If the JSON array is nested, i.e., if it contains JSON objects or arrays itself, you sometimes do not want toextract its full values, but only parts thereof. For instance, if the values of the array are nested JSON objects,you might want to extract only certain values in the leafs of these objects: At this time, the preferred solution in KNIME is to insert an additional JSON Path node. Specifically, we usea first JSON Path node to extract the full values, i.e., the nested JSON objects, from the outer JSON array.Next, we ungroup the resulting collection of nested JSON objects, which results in a KNIME table in whicheach row contains a single nested JSON object. Finally, we use another JSON Path node to extract thevalues which we are actually interested in and which lie in the leafs of these nested objects: If values in the leafs of nested objects are optional and not always present, the JSON Path node emitsmissing values when values are absent. In this example, this is the case for leafs named optionalLeafInMandatoryInnerNode. This is a feature of the Jayway JSONPath library which the JSON Pathnode uses.Unfortunately, the Jayway JSONPath library does not yet provide similar functionality if an inner node of thenested JSON object is optional. In this example, this is the case for the inner node namedoptionalInnerNode which has a leaf named mandatoryLeafInOptionalInnerNode. This missingfunctionality leads to absent missing values in the output of the JSON Path node and, thus, faultyassignments of extracted leaf values to rows in the Ungroup node: extract valuesfrom nested JSON arrayone row per itemextract all valuesin leafs of nested objectsfrom nested JSON arrayextract all valuesin leafs of nested objectsone row per valuein nested JSON array;output faulty!one row per valuein nested JSON arrayextract selected valuesin leafs of nested objectsfrom nested JSON arrayone row per valuein JSON arrayextract valuesfrom JSON array JSON Path JSON Reader Ungroup JSON Path JSON Reader JSON Path Ungroup Ungroup JSON Path JSON Reader JSON Reader Ungroup JSON Path

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: jfalgout
Created at: 2018-04-05
On NodePit since: 2020-01-29
Last update: 2024-04-24
Created with KNIME version: v4.1.0
Tags: JSON PathUngroup

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

Try NodePit Runner!