In this exercise, we will create a Chain that illustrates how to convert a JSON dataset with nested objects to CSV. Also, we will explore illustrate how to use a cartesian join to flatten out this nested structure.
Primary Learning Objective | To highlight the JSON BizApp capability for nested JSON objects. |
Secondary Learning Objectives | This exercise also highlights the Tabular Transformation Advanced Query Command. |
Prerequisites | |
Supporting Template |
Step 1: Create a Chain
From the Sample Workspace for Transformations Workspace, select the DEV Environment.
Add a new Chain.
Name the Chain Transformation QS - JSON Nested Object.
Save the Chain.
Step 2: Retrieve JSON data
Use the HTTP BizApp to retrieve headcount-related data in JSON format from a web location.
Add a GET Command from the HTTP BizApp to the Start node.
Configure the Command with the following:
Name | GET - JSON Data |
User Name | <leave blank> |
Password | <leave blank> |
CA Certificate | <leave blank> |
Certificate | <leave blank> |
Certificate Private Key | <leave blank> |
Show Response | Checked |
URL | donut-json Workspace Variable |
Query string | <leave blank> |
Content type | application/json |
Response | <leave blank> |
Save the Command
Step 3: Get Unnested JSON Data
Use the Object to CSV Command from the JSON BizApp to extract the name and type keys, which are not nested, from the JSON object.
It is important to understand the schema of the JSON dataset. A List File Content Command from the File Utilities BizApp can be utilized to visualize the schema. For reference, below is the schema of the donut JSON:
Schema
{
"id": "0001",
"type": "donut",
"name": "Cake",
"ppu": 0.55,
"batters":
{
"batter":
[
{ "id": "1001", "type": "Regular" },
{ "id": "1002", "type": "Chocolate" },
{ "id": "1003", "type": "Blueberry" },
{ "id": "1004", "type": "Devil's Food" }
]
},
"topping":
[
{ "id": "5001", "type": "None" },
{ "id": "5002", "type": "Glazed" },
{ "id": "5005", "type": "Sugar" },
{ "id": "5007", "type": "Powdered Sugar" },
{ "id": "5006", "type": "Chocolate with Sprinkles" },
{ "id": "5003", "type": "Chocolate" },
{ "id": "5004", "type": "Maple" }
]
}
Add an Object to CSV Command from the JSON BizApp to the Chain.
Connect the Start Node (GET - JSON Data) to the Object to CSV Command.
Name the Command: Object to CSV - Name & Type.
In the JSON Data parameter, select the Response Output from the GET - JSON Data Command.
Leave the Input Text and Path to root parameters blank.
Leave the Multi-value Delimiter parameter as a comma (,).
Check the Preview Result option.
Select Comma for the Delimiter parameter.
The Columns section is used to specify which elements from the JSON object will be extracted to a columnar (CSV) dataset. Click the Add button twice to add two columns.
On the first column, enter name for the Column Name and .name for the JSONPath parameters.
On the second column, enter type for the Column Name and .type for the JSONPath parameters.
Save the Command

Step 4: Get the List of Toppings
Use the Array to CSV Command of the JSON BizApp to get the list of toppings in the nested JSON array.
Add an Array to CSV Command from the JSON BizApp to the Chain.
Connect the GET - JSON Data Command to the Array to CSV Command.
Edit the Command
Name the Command: Array to CSV - Toppings.
In the JSON Data parameter, select the Response Output from the GET - JSON Data Command.
Leave the Input Text parameter blank.
In the Path to root parameter, type topping (in lower case) and press the enter key. Topping should appear in a grey bubble.
Leave the Filter parameter blank.
Leave the default value, comma (,), for the Multi-value Delimiter parameter.
Check the Preview Result option.
In the Columns section, we specify the key(s) in the JSON array for which to extract the value(s) to a column in the resulting CSV.
Add a column. In the Column name parameter, enter topping_type and in the JSONPath parameter, enter .type.
In the Delimiter parameter, select Comma.
Save the Command.

Step 5: Get a List of the Batters
Use the Array to CSV Command of the JSON BizApp to get the list of batters in the nested JSON array.
Use two Array to CSV Commands to extract the batters and toppings because of the different nesting of each array. Using an Object to CSV Command with nested JSONPaths (e.g., .topping[*].type) would have created multi-part values which are more difficult to use in a CSV dataset.
Add an Array to CSV Command from the JSON BizApp to the Chain.
Connect the GET - JSON Data Command to the Array to CSV Command.
Name the Command: Array to CSV - Batters.
In the JSON Data parameter, select the Response Output from the GET - JSON Data Command.
Leave the Input Text parameter blank.
In the Path to root parameter, type batters (in lower case) and press the enter key. Next type batter (lower case) and press the enter key. Batters and batter should appear in this order in two grey bubbles.
Leave the Filter parameter blank.
Leave the default value, comma (,), for the Multi-value Delimiter parameter.
Check the Preview Result option.
In the Columns section, we specify the key(s) in the JSON array for which to extract the value(s) to a column in the resulting CSV.
Add a column. In the Column name parameter, enter batter_type and in the JSONPath parameter, enter .type.
In the Delimiter parameter, select Comma.
Save the Command.

Step 6: Flatten the Data
Use a cartesian join in an Advanced Query Command from the Tabular Transformation BizApp to flatten the dataset. A cartesian join creates all possible combinations of the elements that we extracted using the Object to CSV and Array to CSV Commands.
Add an Advanced Query Command from the Tabular Transformation BizApp to the Chain.
Connect each of the Object to CSV - Name & Type, Array to CSV - Batters, and Array to CSV - Toppings Commands to the Advanced Query Command.
Name the Command: Advanced Query - Flatten JSON Object.
In the Tables section, click the Add button twice so that there are three available tables. Complete the Tables section per the below:
File | Table Name |
Converted file Output from the Object to CSV - Name & Type Command | Name |
Converted file Output from the Array to CSV - Batters Command | Batter |
Converted file Output from the Array to CSV - Toppings Command | Topping |
In the Query parameter, enter the following query:
Select Type as dessert_type,Name as variety,Batter_Type,Topping_Type from Name,Batter,Topping
Specify Comma for the Input Delimiter and Output Delimiter parameters.
Check the Preview results option.
Save the Command Group.

Step 7: Review Results
Use a List File Content Command from the File Utilities BizApp to review the results of parsing and flattening the nested JSON object.
Add a List File Content Command from the File Utilities BizApp to the Chain.
Connect the Advanced Query - Flatten JSON Object Command to the List File Content Command.
Configure the Command with the following:
Name | List File Content - Preview Results |
File Name | Result Output from the Advanced Query - Flatten JSON Object Command |
Preview Lines | 100 |
Offset | 0 |
Publish the Chain.
Click Execute and then select Run Chain.
Once the Chain has completed,
Click the Advanced Query - Flatten JSON Object node and select the Outputs tab. Confirm the Record Count is 28.
Click the List File Content - Preview Results node and select the Logs tab. Confirm the data preview matches the below.
dessert_type,variety,batter_type,topping_type
donut,Cake,Regular,None
donut,Cake,Regular,Glazed
donut,Cake,Regular,Sugar
donut,Cake,Regular,Powdered Sugar
donut,Cake,Regular,Chocolate with Sprinkles
donut,Cake,Regular,Chocolate
donut,Cake,Regular,Maple
donut,Cake,Chocolate,None
donut,Cake,Chocolate,Glazed
donut,Cake,Chocolate,Sugar
donut,Cake,Chocolate,Powdered Sugar
donut,Cake,Chocolate,Chocolate with Sprinkles
donut,Cake,Chocolate,Chocolate
donut,Cake,Chocolate,Maple
donut,Cake,Blueberry,None
donut,Cake,Blueberry,Glazed
donut,Cake,Blueberry,Sugar
donut,Cake,Blueberry,Powdered Sugar
donut,Cake,Blueberry,Chocolate with Sprinkles
donut,Cake,Blueberry,Chocolate
donut,Cake,Blueberry,Maple
donut,Cake,Devil's Food,None
donut,Cake,Devil's Food,Glazed
donut,Cake,Devil's Food,Sugar
donut,Cake,Devil's Food,Powdered Sugar
donut,Cake,Devil's Food,Chocolate with Sprinkles
donut,Cake,Devil's Food,Chocolate
donut,Cake,Devil's Food,Maple

📚 Topics covered in this exercise: