Skip to main content

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

  1. 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.

  2. 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