In this exercise, we will create a Chain that illustrates how to convert a more JSON dataset to CSV. In this example dataset, there is an array of nested objects.
This exercise reinforces the looping concept that we learned in the JSON Data exercise as well as the cartesian join concept in the JSON Nested Objects exercise. If you have not completed these exercises, we strongly recommend that you complete these prior to completing this exercise.
Primary Learning Objective | To highlight the JSON BizApp capabilities. |
Secondary Learning Objectives | This exercise also highlights the power of the Tabular Transformation Advanced Query Command, iteration, and variable transformation. |
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 Array of Nested Objects.
Save the Chain.
Step 2: Retrieve JSON Data
Use the HTTP BizApp to retrieve donut data from a web location. This dataset illustrates is an example of a more complex JSON dataset with an array (multiple items) of nested objects (attributes).
Add a GET Command from the HTTP BizApp to the Start node.
Configure the Command with the following:
Name | GET - All Donuts |
User Name | <leave blank> |
Password | <leave blank> |
CA Certificate | <leave blank> |
Certificate | <leave blank> |
Certificate Private Key | <leave blank> |
Show Response | Checked |
URL | all-donuts-json Workspace Variable |
Query string | <leave blank> |
Content type | application/json |
Response | <leave blank> |
Save the Command
Step 3: Convert JSON to CSV
Use the JSON BizApp to extract keys from the JSON array to identify the different donut varieties.
For reference, below is the schema of the all-donuts 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" }
]
},
{
"id": "0002",
"type": "donut",
"name": "Raised",
"ppu": 0.55,
"batters":
{
"batter":
[
{ "id": "1001", "type": "Regular" }
]
},
"topping":
[
{ "id": "5001", "type": "None" },
{ "id": "5002", "type": "Glazed" },
{ "id": "5005", "type": "Sugar" },
{ "id": "5003", "type": "Chocolate" },
{ "id": "5004", "type": "Maple" }
]
}
]
Add an Array to CSV Command from the JSON BizApp to the Chain.
Connect the Start Node (GET - All Donuts) to the Array to CSV Command.
Name the Command: Array to CSV - Get Variety.
In the JSON Data parameter, select the Response Output from the GET - All Donuts Command.
Leave the Input Text, Path to root, and Filter parameters blank.
Leave the Multi-value Delimiter parameter as a comma (,).
Check the Preview Result option.
The Columns click the Add button to add a column.
Enter variety for the Column Name and .name for the JSONPath parameters.
Select Comma for the Delimiter parameter.
Save the Command
Step 4: Create an Iterable Data Set
As noted, the dataset has an array (i.e., multiple items) of nested objects. To process each of the varieties individually, row numbers must be added to the dataset. These row numbers will be used in subsequent Array to CSV Commands to identify the array item (donut) for which the nested object (attributes) will be parsed.
For those familiar with OneCloud Integration Studio, you may wonder why we use Advanced Query as opposed to the Add Row Numbers Command. The Add Row Numbers Command starts counting at one (1) and we need to start counting at zero (0) since JSON Arrays are zero-based.
Add an Advanced Query Command from the Tabular Transformation BizApp to the Chain.
Connect the Array to CSV - Get Variety Command to the Advanced Query Command.
Name the Command: Advanced Query - Add Row Numbers.
Configure the Tables section
In the File field, specify the Converted File Output from the Array to CSV - Get Variety Command.
Enter varieties in the Table Name parameter. The query that is specified will be run against this table.
In the Query parameter, enter the following:
SELECT
ROW_NUMBER () OVER (
ORDER BY variety
)-1 RowNum,
variety
FROM
varieties
Specify Comma for the Input Delimiter and Output Delimiter parameters.
Check the Preview results parameter
Save the Command.
Step 5: Create a JSON Array from the Country List
Use the CSV to JSON Command from the JSON BizApp to convert the unique list of varieties to a JSON array. A Command Group can then iterate over each item in this array.
Add a CSV to JSON Command from the JSON BizApp to the Chain.
Connect the Advanced Query - Add Row Numbers Command to the CSV to JSON Command.
Configure the Command using the following:
Name | CSV to JSON - Varieties |
Input File | Result Output from the Advanced Query - Add Row Numbers Command |
Delimiter | Comma (,) |
Save the Command
Step 6: Add a Command Group
Add a Command Group to the Chain to enable iteration over each of the donut varieties identified in the Advanced Query step.
Add a Command Group to the Chain.
Connect the CSV to JSON - Varieties Command to the In section of the Command Group.
Click the Command Group and then select the dropper icon to change the Command color to yellow.
Name the Group: Donut Varieties.
Enable the Iterations toggle and select the JSON File Output from the CSV to JSON - Varieties Command.
Save the Command Group.
Step 7: 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. This action is repeated for each of the donut varieties.
⚠️ Please pay particular attention to the cautionary statement in the below steps indicated by the red exclamation point.
Add an Array to CSV Command from the JSON BizApp to the Chain.
Connect the Group Start from the Donut Varieties Command Group to the Array to CSV Command.
Name the Command: Array to CSV - Toppings.
In the JSON Data parameter, select the Response Output from the GET - All Desserts Command.
Leave the Input Text parameter blank.
In the Path to root parameter, we will leverage the iteration value to specify the array item on which the iteration will operate.
❗ The following steps are critical and should be read in their entirety before completing this step.
Click in the Path to root parameter. From the Variable selection pane, expand the Group Iterator and click JSON File Iteration.
Do not press the enter key, instead, click the white JSON File Iteration bubble in the Path to root parameter. This will open the Variable transformation form. Select Get Value from JSON and click the plus (+) sign. Type RowNum (case sensitive) In the Value field and press the enter key. Click Accept.
The variable should have an asterisk indicating a Variable transformation has been applied and still be in a white bubble. Press the enter key and the white bubble will change to a grey bubble.
🛑 If any of these steps fail, delete the JSON File Iteration Variable from the Path to root and start again.
After the JSON File Iteration bubble, type topping (in lower case) and press the enter key. Topping should also 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 and in the JSONPath parameter, enter .type.
In the Delimiter parameter, select Comma.
Save the Command.

Step 8: Get the List of Batters
Use the Array to CSV Command of the JSON BizApp to get the list of batters in the nested JSON array. This action is repeated for each of the donut varieties. Since this step is very similar to the previous step, copy the Command configured in the prior step and simply update it for the batters information.
Copy the Array to CSV - Toppings Command.
Connect the Group Start from the Donut Varieties Command Group to the Array to CSV - Toppings (Copy) Command.
Name the copied Command: Array to CSV - Batters.
In the Path to root parameter, delete the topping bubble by clicking the X. Type batters (case-sensitive) and press enter. Then type batter and press enter. There should be three (3) grey bubbles in the Path to root parameter.
JSON File Iteration *
batters
batter
In the Columns section, change the Column Name from topping to batter.
Save the Command.
Step 9: Flatten the Data
Similar to the last exercise, use a cartesian join in an Advanced Query Command from the Tabular Transformation BizApp to flatten the dataset.
Add an Advanced Query Command from the Tabular Transformation BizApp to the Chain.
Connect the 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 once so that there are two available tables. Complete the Tables section per the below:
File | Table 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 below query:
<JSON FILE ITERATION>
should be selected and replaced by expanding Group Iterator from the Variable pane and selecting JSON File Iteration.After the JSON File Iteration Variable is selected, apply the Get Value from JSON Variable transformation using variety for the Value field.
Select '<JSON FILE ITERATION>' as Variety,Batter,Topping from Batter,Topping
Specify Comma for the Input Delimiter and Output Delimiter parameters.
Check the Preview results option.
Save the Command Group.

Step 9: Test the Exercise
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 for each iteration:
Iteration 1: 28 records
Iteration 2: 5 records
Iteration 3: 8 records
Click the List File Content - Preview Results node and select the Logs tab. Confirm the data matches for each iteration.
Iteration 1
Variety,batter,topping
Cake,Regular,None
Cake,Regular,Glazed
Cake,Regular,Sugar
Cake,Regular,Powdered Sugar
Cake,Regular,Chocolate with Sprinkles
Cake,Regular,Chocolate
Cake,Regular,Maple
Cake,Chocolate,None
Cake,Chocolate,Glazed
Cake,Chocolate,Sugar
Cake,Chocolate,Powdered Sugar
Cake,Chocolate,Chocolate with Sprinkles
Cake,Chocolate,Chocolate
Cake,Chocolate,Maple
Cake,Blueberry,None
Cake,Blueberry,Glazed
Cake,Blueberry,Sugar
Cake,Blueberry,Powdered Sugar
Cake,Blueberry,Chocolate with Sprinkles
Cake,Blueberry,Chocolate
Cake,Blueberry,Maple
Cake,Devil's Food,None
Cake,Devil's Food,Glazed
Cake,Devil's Food,Sugar
Cake,Devil's Food,Powdered Sugar
Cake,Devil's Food,Chocolate with Sprinkles
Cake,Devil's Food,Chocolate
Cake,Devil's Food,Maple
Iteration 2
Variety,batter,topping
Old Fashioned,Regular,None
Old Fashioned,Regular,Glazed
Old Fashioned,Regular,Sugar
Old Fashioned,Regular,Chocolate
Old Fashioned,Regular,Maple
Iteration 3
Variety,batter,topping
Raised,Regular,None
Raised,Regular,Glazed
Raised,Regular,Chocolate
Raised,Regular,Maple
Raised,Chocolate,None
Raised,Chocolate,Glazed
Raised,Chocolate,Chocolate
Raised,Chocolate,Maple
📚 Topics covered in this exercise: