JSON Parse Tool (Live Query for Google BigQuery)
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 Availability
The JSON Parse tool is available in the Developer tool palette.
Before using this tool...
From a Cloud Native workflow, go to Options.
Verify that Enable LiveQuery is enabled.
Verify that the Selected Connection displays a BigQuery connection.
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.
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. If you have a wide JSON tree, select the Open in separate window icon to open the structure in a resizable window.
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. From the structure section, select the settings icon and then select Select All Elements to Unnest.
Malformed JSON Handling
Choose how the tool handles malformed JSON values 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.