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 and JSON_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 columns hours.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 to hours.Tuesday.close and hours.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

In this section:

JSON Parse Tool

Configure the Tool

Search results