JSON Parse Tool
Use JSON Parse to separate JavaScript Object Notation (JSON) text into a table schema for downstream processing.
Note
The Input Data tool doesn't support the direct upload of the JSON file type. To use JSON data in your workflow, upload a JSON file on the Data page. You can then select the uploaded file from the Input Data tool.
Configure the Tool
Column with JSON to Parse: Select the column that holds JSON text. The column has to contain valid JSON with all the text for a row in a single cell.
Here's an example:
{ "firstName": "John", "lastName": "Smith", "age": 25, "address": { "streetAddress": "21 2nd Street", "city": "New York", "state": "NY", "postalCode": "10021" }, "phoneNumber": [ { "type": "home", "number": "212 555-1234" }, { "type": "fax", "number": "646 555-4567" } ] }
Include in Output: The selected column is included in the data stream coming out of the tool.
Select the Output Options:
Output Values into Single String Column: Output 2 columns:
JSON Name
andJSON_ValueString
. Looking at the previous input example, this data comes out of the tool:JSON_Name
JSON_ValueString
firstName
John
lastName
Smith
age
25
address.streetAddress
21 2nd Street
address.city
New York
address.state
NY
address.postalCode
10021
phoneNumber.0.type
home
phoneNumber.0.number
212-555-1234
phoneNumber.1.type
fax
phoneNumber.1.number
646-555-4567
Output Values into Data Type-Specific Columns: Output JSON key-value pairs. Values populate as string, integer, double, and boolean columns based on the JSON data type.
Column Name
Description
JSON_Name
The JSON_Name is the 'key' of a JSON Object (key:value pair). A dot separates the key and any hierarchical categories.
JSON_Value String
The corresponding string 'value' of the JSON Object (key:value pair). If the value is not a string, it will display as [Null].
JSON_ValueInt
The corresponding integer 'value' of the JSON Object (key:value pair). If the value is not an integer, it will display as [Null].
JSON_ValueFloat
The corresponding float 'value' of the JSON Object (key:value pair). If the value is not a float, it will display as [Null].
JSON_ValueBool
The corresponding bool 'value' of the JSON Object (key:value pair). If the value is not a bool, it will display as [Null].
Looking at the previous example, this data comes out of the tool:
JSON_Name
JSON_ValueString
JSON_ValueInt
JSON_ValueFloat
JSON_ValueBool
firstName
John
[Null]
[Null]
[Null]
lastName
Smith
[Null]
[Null]
[Null]
age
[Null]
25
[Null]
[Null]
address.streetName
21 2nd Street
[Null]
[Null]
[Null]
address.city
New York
[Null]
[Null]
[Null]
address.state
NY
[Null]
[Null]
[Null]
address.postalCode
10021
[Null]
[Null]
[Null]
phoneNumber.0.type
home
[Null]
[Null]
[Null]
phoneNumber.0.number
212-555-1234
[Null]
[Null]
[Null]
phoneNumber.1.type
fax
[Null]
[Null]
[Null]
phoneNumber.1.number
646-555-4567
[Null]
[Null]
[Null]
Unnest JSON Field: Unnest JSON objects into columns. It goes only 1 level deeper into the JSON object. Refer to this example...
JSON Input
{ "Tuesday": {"close": "17:00", "open": "08:00"}, "Friday": {"close": "17:00", "open": "08:00"}, "Monday": {"close": "17:00", "open": "08:00"}, "Wednesday": {"close": "17:00", "open": "08:00"}, "Thursday": {"close": "17:00", "open": "08:00"} }
JSON Parse Tool Output: Example output when you unnest the
hours
column.hours.Tuesday
hours.Friday
hours.Monday
hours.Wednesday
hours.Thursday
{"close":"17:00","open":"08:00"}
{"close":"17:00","open":"08:00"}
{"close":"17:00","open":"08:00"}
{"close":"17:00","open":"08:00"}
{"close":"17:00","open":"08:00"}
Unnest on the
hours
column outputs columnshours.Tuesday
,hours.Friday
,hours.Monday
and so on.hours.Tuesday
continues to carry the object{"close": "17:00", "open": "08:00"
}. You can unnest this again tohours.Tuesday.close
andhours.Tuesday.open
with another JSON Parse tool.
Flatten Array: This option is applicable for columns that have array values only. Use this option to expand a JSON array column by removing the square brackets. It creates a separate row for each element separated by a comma and assigns an ID for each row. Refer to this example...
JSON Input
[ {"day": "Monday", "open": "08:00", "close": "17:00"}, {"day": "Tuesday", "open": "08:00", "close": "17:00"}, {"day": "Wednesday", "open": "08:00", "close": "17:00"}, {"day": "Thursday", "open": "08:00", "close": "17:00"}, {"day": "Friday", "open": "08:00", "close": "17:00"} ]
JSON Parse Tool Output: Consider this array input in square brackets where the column name is
hours
and the column value is an array.hours_flatten
hours_idx
{"day":"Monday","open":"08:00","close":"17:00"}
1
{"day":"Tuesday","open":"08:00","close":"17:00"}
2
{"day":"Wednesday","open":"08:00","close":"17:00"}
3
{"day":"Thursday","open":"08:00","close":"17:00"}
4
{"day":"Friday","open":"08:00","close":"17:00"}
5