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]