Skip to main content

JSON Parse Tool

Use the JSON Parse tool to extract structured data from JSON stored in a string column. The tool analyzes the JSON structure and lets you extract elements for downstream processing.

JSON Parse supports nested objects and arrays. Based on your selections, the tool can flatten objects into columns, expand arrays into multiple rows, or preserve sections of the JSON as strings.

Use this tool when working with data returned from APIs, web services, cloud platforms, log files, and AI services that return results in JSON format.

Tool Configuration

Select JSON Column

Use the Column with JSON to Parse dropdown to select the column that contains JSON-formatted text. After you select a column, the tool analyzes the JSON structure using sample data from the incoming dataset. The tool returns an error if the sample data does not contain a proper JSON structure.

The tool infers the JSON structure from the first 10 rows of the incoming data.

If additional fields or different JSON structures appear later in the dataset, they might not be included in the inferred structure.

Advanced Settings

Use Custom Schema

Select the Use Custom Schema option under Advanced Settings to use a custom JSON Schema instead of the inferred schema.

Note

A schema defines the structure of the data by specifying the fields, their data types, and how the data is organized.

Example: A schema can define fields such as customer_name (string), customer_id (integer), and order_date (date).

Upload a custom JSON Schema file to define the structure used by the tool. After you upload or edit a custom schema, apply it to the JSON structure.

Restore Structure

Restore the automatically inferred schema at any time.

Schema Validation

The tool validates the custom schema before applying it.

After you upload or edit a custom schema, select Apply to use the schema in the JSON structure.

The tool validates the schema for:

  • Invalid JSON

  • Duplicate keys

  • Unsupported references

  • Standard JSON submitted instead of JSON Schema

If the uploaded or edited schema is not valid, the tool displays a validation error.

If you upload or paste a standard JSON document instead of a JSON Schema, the tool displays an error.

Schema Format Tooltip

A tooltip is available to explain the expected JSON Schema format.

Select the Display JSON Path in Structure checkbox to include the JSON path of each leaf in the structure below.

Review JSON Structure

The JSON structure of the selected column appears as a tree in the Structure section of the configuration panel.

Select elements in the tree to determine which values the tool extracts. Arrays are represented by wildcard paths (for example, [*]). Individual array indexes cannot be selected. When selecting array elements, only one array branch can be selected at a time.

If you have a wide JSON tree, select the Open in separate window icon to open the structure in a resizable window

Open JSON Structure in Separate Window

You can open the JSON structure in a separate window for easier navigation.

To pass the original JSON through the tool in a separate column, select the Root checkbox in the structure.

You can also automatically select all leaf nodes in the structure to extract all terminal values.

Select All / Deselect All

You can also automatically select all leaf nodes in the structure to extract all terminal values.

Use Deselect All to clear all selected structure elements.

Expand All / Collapse All

Use Expand All to expand the complete structure tree.

Use Collapse All to collapse the complete structure tree.

Malformed JSON Handling

Choose how the tool handles malformed JSON values during processing.

  • Invalid JSON Handling: Use the Invalid JSON Handling setting to control how the tool handles invalid JSON during processing.

  • Error (Stop Data Processing): Processing stops when the tool encounters a malformed JSON value.

    • An error message appears in the Results window.

    • The workflow stops processing additional rows.

  • Ignore (Continue Data Processing): Malformed JSON values are skipped.

    • The tool returns a null value for the affected record.

    • Processing continues for the remaining rows.

    • No error message appears.

Output

The output structure depends on the elements you select in the JSON tree.

For each selected element, the tool creates a column. The column name represents the full path to the element in the JSON hierarchy. Each level in the path is separated by an underscore (_).

Example:

customer_address_city

Depending on the selected element type, the output column contains either a parsed value or a JSON string.

The tool preserves native data types when possible, including:

  • string

  • integer

  • float

  • boolean

  • null

When you expand arrays into rows, the tool automatically generates index values to represent element order. This appears as a new column appended with _ind.

Column Naming

Column names can include spaces and special characters if they appear in the JSON keys. However, you might need to rename these fields before writing results to certain destinations.

The maximum column name length is 300 characters. If a generated column name exceeds this limit, the tool returns an error.

Input Requirements

  • The input must include a column with string values that contain valid JSON according to the ISO/IEC 21778:2017 standard and follow BigQuery JSON rules.

  • The tool assumes that the JSON schema remains relatively stable across rows. It uses sample records from the beginning of the dataset to infer the structure used for configuration.

  • Empty values and null values in the input produce null values in the output and do not stop processing.

In this section: