Workiva Support Center

In this Connected Learning Path, we will create a Chain that illustrates how to convert a simple JSON dataset to CSV. Also, we will explore how to loop over a JSON dataset and filter based on values in the data.

Step 1: Create a Chain

• Add a new Chain
  • Name the Chain CLP | Accessing JSON Data
• Create Chain variable:
  • Under Chain Variables, click the Plus Sign
    • Name: cv-JSON-Employee
    • Value: https://cs-sftp-training-bucket.s3.amazonaws.com/cs-training/transformation-qs/emp_detail.json
• Save the Chain

Step 2: Retrieve JSON Data

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

• Add a GET Command from the HTTP Connector to the Start node
• Configure the Command with the following:

• Save the Command

Step 3: Convert JSON to CSV

Use the JSON Connector to convert the JSON dataset to a CSV format. Select only the employee ID and country fields from the JSON array in this step.
 
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. You can also add the schema to the Response field in the Get Command for easy reference. Below is the schema of the employees JSON:

Schema:

[
 
 {
   "emp_id": "26-4992011",
 
   "user_name": "apostle0",
 
   "first_name": "Audi",
 
   "last_name": "Postle",
 
   "job_title": "Chief Design Engineer",
 
   "department": "Marketing",
 
   "city": "Cheyenne",
 
   "country": "United States",
 
   "currency": "USD",
 
   "hire_date": "08/30/2000",
 
   "fte": 1,
 
   "termed": false
 
  }

]

• Add an Array to CSV Command from the JSON Connector to the Chain.
• Connect the Start Node (GET - All Employees) to the Array to CSV Command.
• Name the Command: Array to CSV - All Employees.
• In the JSON Data parameter, select the Response Output from the GET - All Employees 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 section is used to specify which elements from the JSON array will be extracted to a columnar (CSV) dataset. Click the Add button once to add an additional column.
  • On the first column, enter emp_id for the Column Name and .emp_id for the JSONPath parameters.
    • ❗ Be sure to include the dot (.) before emp_id in the JSONPath parameter.
  • On the second column, enter country for the Column Name and .country for the JSONPath parameters.
  • Select Comma for the Delimiter parameter.
• Save the Command

Step 4: Get the Unique List of Countries in the Data

Use a simple Select Distinct statement with the Advanced Query Command of the Tabular Transformation Connector to get the unique set of countries in the data. Later in this exercise, this list will be used to retrieve the employees by each country individually.

• Add an Advanced Query Command from the Tabular Transformation Connector to the Chain.
• Connect the Array to CSV - All Employees Command to the Advanced Query Command.
• Name the Command: Advanced Query - Countries.
• The Tables section allows one or more delimited datasets to be utilized to create tables against which SQL statements can be written. A SQLite database with the tables specified is generated on the fly, no additional software installation is required.
  • In the File field, specify the Converted File Output from the Array to CSV - All Employees Command.
  • Enter Countries in the Table Name parameter. The query that is specified will be run against this table.
• In the Query parameter, enter the following:



 Select Distinct Country from Countries

• 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

We use the CSV to JSON Command from the JSON Connector to convert the unique list of countries 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 Connector to the Chain.
• Connect the Advanced Query - Countries Command to the CSV to JSON Command.
• Configure the Command using the following:

• Save the Command

Step 6: Add a Command Group

Add a Command Group to the Chain to enable iteration over each of the individual countries identified in the Advanced Query step.

• Add a Command Group to the Chain.
• Connect the CSV to JSON - Countries 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 orange.
• Name the Group: Active Employees.
• Enable the Iterations toggle under the iterations tab and select the JSON File Output from the CSV to JSON - Countries Command.
• Save the Command Group.

Step 7: Filter JSON Data for Employees by Country

Convert the JSON dataset to CSV while filtering only the employee records for the country currently being processed in the iteration. This step highlights two powerful capabilities - the ability to loop or iterate and the ability to apply filters to the JSON conversion process. Understanding these capabilities bolsters one's ability to develop robust, efficient Chains.
 
Copy the previously configured Array to CSV Command for this step and the configuration leverages largely the same configuration except where noted below.

• Copy the Array to CSV - All Employees Command.
• Connect the Group Start from the Command Group to the Array to CSV - All Employees (Copy) Command.
• Edit the Command.
• Name the Command: Array to CSV - Employees by Country.
• In the Filter parameter, enter the below:




 ?(@.country == "<JSON FILE ITERATION>")

• • The space before and after the double equals (==) sign are required.
  • <JSON FILE ITERATION> should be replaced with the JSON File Iteration under the Group Iterator in the Variable Pane.
    • When adding the JSON File Iteration, click the Variable bubble and add a Get Value from JSON Variable Transformation.
    • In the Value parameter of the Variable Transformation, enter country and then press the Enter key. Remember, JSON is case-sensitive.


• Edit the emp_id column to have a Column Name of employee_id. Do not change the JSONPath.
  • The Column Name generated by the Command does not need to match the JSON Array key.
• Add 10 Columns to the Command and configure according to the below:

• Save the Command.

Step 8: Test the Exercise

Now that the Chain is complete, test the result.

• Publish the Chain.
• Click Execute and then select Run Chain.
• Once the Chain has completed, click the Array to CSV - Employees by Country node.
  • Notice that the iteration ran five (5) times.
  • Select each iteration value and then click the Inputs tab. Confirm that the Filter matches the below table.
  • Select iteration 5 and click the Logs tab. Confirm the data preview matches the image below.