In this exercise, 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.

Primary Learning Objective

To highlight the JSON BizApp capability.

Secondary Learning Objectives

This exercise also highlights the Tabular Transformation Advanced Query Command as well as iteration.

Prerequisites

Supporting Template

JSON Data


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 Data.

  • Save the Chain.


Step 2: Retrieve JSON Data

We 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 - All Employees

User Name

<leave blank>

Password

<leave blank>

CA Certificate

<leave blank>

Certificate

<leave blank>

Certificate Private Key

<leave blank>

Show Response

Checked

URL

employee-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 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 BizApp can be utilized to visualize the schema. For 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 BizApp 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 twice to add two columns.

    • 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 BizApp 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 BizApp 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 by Integration Studio and 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 BizApp 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 BizApp to the Chain.

  • Connect the Advanced Query - Countries Command to the CSV to JSON Command.

  • Configure the Command using the following:

Name

CSV to JSON - Countries

Input File

Result Output from the Advanced Query - Countries 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 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 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:

    • 📓 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.

?(@.country == "<JSON FILE ITERATION>")
  • 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:

Column Name

JSONPath

user_name

.user_name

first_name

.first_name

last_name

.last_name

title

.job_title

department

.department

city

.city

currency

.currency

hire_date

.hire_date

fte

.fte

terminated

.termed

  • 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.

Iteration 1

United States

Iteration 2

Brazil

Iteration 3

Kenya

Iteration 4

Italy

Iteration 5

Australia


📚 Topics covered in this exercise:

Command Groups

Iteration

HTTP

JSON

Tabular Transformation


Did this answer your question?