Workiva Support Center

In this Connected Learning Path, we will create a Chain that illustrates how to convert a JSON dataset with nested objects to CSV. Also, we will explore how to use a cartesian join to flatten out this nested structure.

Step 1: Create a Chain

1. Add a new Chain.
   1. Name the Chain: CLP | Accessing JSON Nested Objects.
   2. Create a chain variable, and populate it using the following values:
      • Name: cv-JSON-Donut
      • Value: https://cs-sftp-training-bucket.s3.amazonaws.com/cs-training/transformation-qs/donut.json
2. Save the Chain.

Step 2: Retrieve JSON data

Use the HTTP Connector to retrieve headcount-related data in JSON format from a web location.

1. Add a GET Command from the HTTP Connector to the Start node.
2. Configure the Command using the following values:

   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	cv-JSON-Donut Chain Variable
   Query string	<leave blank>
   Content type	application/json
   Response	<leave blank>

3. Save the Command.

Step 3: Get Un-Nested JSON Data

This chain uses the Object to CSV Command from the JSON Connector to extract the name and type keys, which are not nested, from the JSON object.

Note: It is important to understand the schema of the JSON dataset. A List File Content Command from the File Utilities Connector can be utilized to visualize the schema. For reference, here 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"}]
} 

1. Add an Object to CSV Command from the JSON Connector to the Chain.
2. Connect the Start Node (GET - JSON Data) to the Object to CSV Command.
3. Open the Command, and configure it using the following values.
   • Name the Command: Object to CSV - Name & Type.
   • For 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 (,).
   • Mark the Preview Result checkbox.
   • Select Pipe 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 once to add two columns.
     • For the first column, enter name for the Column Name and .name for the JSONPath parameters.
     • For the second column, enter type for the Column Type and .type for the JSONPath parameters.
4. Save the Command.

Step 4: Get the List of Toppings

Use the Array to CSV Command of the JSON Connector to get the list of toppings in the nested JSON array.

1. Add an Array to CSV Command from the JSON Connector to the Chain.
2. Connect the GET - JSON Data Command to the Array to CSV Command.
3. Configure the Command using the following values:
   • Name the Command: Array to CSV - Toppings.
   • For the JSON Data parameter, select the Response Output from the GET - JSON Data Command.
     
   • Leave the Input Text parameter blank.
   • For the Path to root parameter, type topping (in lower case) and press Enter. Topping should appear with an equals sign before it in a grey bubble.
     
   • Leave the Filter parameter blank.
   • Leave the default value of comma (,) for the Multi-value Delimiter parameter.
   • Mark the Preview Result checkbox.
   • 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.
     • For the first Column name parameter, enter ToppingID, and in the JSONPath parameter, enter .id.
     • For the second Column name parameter, enter ToppingType, and in the JSONPath parameter, enter .type.
   • For the Delimiter parameter, select Pipe.
4. Save the Command.

Step 5: Get a List of the Batters

This chain uses the Array to CSV Command in the JSON Connector to get the list of batters in the nested JSON array. We will 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 (for example, .topping[*].type) creates multi-part values, which are more difficult to use in a CSV dataset.

1. Add an Array to CSV Command from the JSON Connector to the Chain.
2. Connect the GET - JSON Data Command to the Array to CSV Command.
3. Configure the Command using the following values:
   • Name the Command: Array to CSV - Batters.
   • For the JSON Data parameter, select the Response Output from the GET - JSON Data Command.
     
   • Leave the Input Text parameter blank.
   • For the Path to root parameter, type batters (all lower case) and press Enter. Then type batter (all lower case) and press Enter.
     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.
     • For the Column name parameter, enter BatterID and in the JSONPath parameter, enter .id.
     • For the Column name parameter, enter BatterType and in the JSONPath parameter, enter .type.
     • For the Delimiter parameter, select Pipe.
       
4. Save the Command.

Step 6: Flatten the Data

We will use a "Cartesian join" in an Advanced Query Command from the Tabular Transformation Connector 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.

1. Add an Advanced Query Command from the Tabular Transformation Connector to the Chain.
2. Connect each of the Object to CSV - Name & Type, Array to CSV - Batters, and Array to CSV - Toppings Commands to the Advanced Query Command.
3. Configure the command using the following values:
   • 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 using the following values:

     File	Table Name
     Select converted file Output from the Object to CSV - Name & Type Command.	Name
     Select converted file Output from the Array to CSV - Batters Command.	Batter
     Select converted file Output from the Array to CSV - Toppings Command.	Topping

     

   • For the Query parameter, enter the following:
     Select Type as dessert_type, Name as variety, BatterType, ToppingType from Name, Batter, Topping
   • For the Input Delimiter and Output Delimiter parameters, specify Pipe.
   • Mark the Preview results checkbox.
     
4. Save the Command.

Step 7: Test the Chain and Review Results AAA

1. Publish the Chain.
2. Click Execute and then select Run Chain.
3. Once the Chain has completed,
   1. Click the Advanced Query - Flatten JSON Object node and select the Outputs tab.
   2. Confirm the Record Count is 28.
      
   3. Select the Logs tab, and confirm the data preview matches the following screenshot.
      

To learn more about data transformation using Chains, check out Connected Learning Paths - Transformation Introduction.