Workiva Support Center

Background

Chains interacts with a variety of 3rd party systems as well as the Workiva platform by leveraging the REST API of the system with which it is integrating.  Many REST API responses are in JSON format including the various Workiva APIs.  While a JSON response may be well documented and consistently structured, it is difficult to work with data in this format.  The Array to CSV Command in the JSON Connector is a useful way to convert a JSON payload into a tabular (rows & columns) format for use within the Workiva platform. 

In this article we explore how to use the filtering capabilities of this Command to return only the results from the JSON payload that are needed.  This can be necessary because a REST API response often contains more information than needed since the payload is generally determined by developers and end users of the endpoint often do not have the ability to limit the scope of the data returned. 

As an example, the List Files Command of the Workiva Connector uses the Retrieve a List of Files endpoint from the Workiva Wdata API to return a list of all of the files associated with a Wdata table.  The JSON response for this endpoint contains the following information:

 [ 
   { 
     "created": "2024-01-24T12:01:53.000Z", 
     "id": "89b34ac927fe499abf944571ded77983", 
     "metadata": { 
         "column1": {}, 
         "column2": {}
         }, 
     "name": "executions_2024-01.csv", 
     "numRecords": 108808, 
     "originalFileSize": 26226080, 
     "status": "IMPORTED", 
     "tableId": "bafe350ebff74a2dbc11262245483dee", 
     "updated": "2024-01-24T12:02:03.000Z", 
     "userId": "HzU1OTQwMTMxODMzMTE4NzI", 
     "version": 1 
   },
   { 
     "created": "2023-12-18T15:06:56.000Z", 
     "id": "62f3d0b79b724776a4fae8bf25f711f1", 
     "metadata": { 
        "column1": {}, 
        "column2": {}
        }, 
     "name": "executions_2023-12.csv", 
     "numRecords": 101789, 
     "originalFileSize": 24226080, 
     "status": "IMPORTED", 
     "tableId": "bafe350ebff74a2dbc11262245483dee", 
     "updated": "2023-12-18T15:06:56.000Z", 
     "userId": "HzU1OTQwMTMxODMzMTE4NzI", 
     "version": 1 
   }
]

The Array to CSV Command allows us to select only the fields that we can to retrieve from the JSON response.  For example, we can chose to select the id, name, and status fields by specifying those on the Columns input section of the Command.  The Command will extract every file ID, Name, and Status from the JSON response and create a row for each file in the JSON response.  

Continuing with the List Files example above, a common use case within the Workiva platform is needing to update the data (file) in a Wdata table.  To import an updated dataset (file), the existing dataset needs to be removed from the table because Wdata does not allow multiple files with the same name.  To accomplish a delete of the existing file, ID and status (Staged or Imported) of the existing file needs to be retrieved. 

One approach to retrieve this information is to convert the List Files JSON payload to a tabular format using the Array to CSV Command.  Next you would use the Tabular Transformation Smart Filter Rows Command to isolate the individual row with the file information.  Then you would use the Tabular Transformation Extract Value Command to retrieve the File ID and Status.  For the purpose of this article, we'll call this the Legacy approach.   

Using Filters

The Filter input allows a JSON response to be filtered not only in terms of the fields (columns) to be selected but also which items (rows) will be retrieved from JSON payload.  This is a powerful mechanism to select the specific data needed which improves the efficiency of the integration process.  The Filter allows one or more elements in the JSON payload to be used to limit the number of items (rows) retrieved. 

A filter criteria has 3 components - element path, comparison value, and an operator.  The criteria must resolve to a true or false value.  When the filter criteria returns a true value, the record is extracted from the JSON payload according to the Command Columns definition and created as a row in the resulting CSV output.  For example:

• Does the file status equal Staged?

In this example, file status is the element path, equal is the operator, and Staged is the Comparison Value.  Any record in the JSON payload where the file status is Staged, a row is created in the CSV containing the columns definition in Command configuration.  For this example they are ID, name, and status.

Element Path

To specify a filter, the path to the element must be known.  For example in the List Files response, the path to the Status element is:

@.status

The value associated with the element is retrieved during Command execution.  In the payload example above this would be the value IMPORTED for the status element. 

Note: An element path must begin the at (@) symbol.

Comparison Value

The Comparison Value is the value against which the Element Path value is compared.  The Comparison Value can be a static value defined in the Command configuration or it can utilize a Variable or Output value that is determined at execution.  

Operator

The operator determines the type of evaluation that will be performed to filter the data. 

Note: A space is required between the Element Path and the Operator as well as between the Operator and the Comparison value.

Supported Operators

It is important to consider the type of values that exist for the element path when specifying the operator.  For example, use of the greater than (>) operator would potentially produce a blank output when evaluating an element that contains text values since a numeric evaluation of a text value is illogical.    

Filter Syntax & Example Filters

All filters must be begin with a question mark (?) and be contained within parentheses.  

Single Criteria

A single criteria filter criteria utilizes the following syntax:

?(Element_Path Operator Comparison_Value)

?(@.name == "executions_2023-12.csv")

This filter returns all records where the name element equals executions_2023-12.csv.

Multiple Criteria

A multiple criteria filter utilizes the following syntax:

?(Element_Path Operator Comparison_Value Logical_Operator Element_Path Operator Comparison_Value)

?(@.name == "executions_2023-12.csv" && @.status == "IMPORTED")

This filter returns all records where the name element equals executions_2023-12.csv and the status element equals IMPORTED.

Example Command Configuration

In this example, the Filter input is configured to evaluate the name element in the JSON payload.  The Filter is using Variables for the Year and Month which in this example are Runtime Input values.  This enables the filter to dynamically account for the year and month specified when the Chain executes.  

The Value of Filters

Filters allow only the required rows to be retrieved from a JSON response.  This can reduce the size of the Output file generated by the Command which can improve Chain performance. 

Additionally, when attempting to isolate a single row from a JSON response, Dynamic Outputs can be used to extract those specific values from the filtered payload and make them available for use in subsequent nodes of the Chain. 

The below shows an example Dynamic Output definitions.

The resulting Chain is more streamlined with a single node (Array to CSV) performing the operations of four nodes required with a legacy approach.  This streamlined Chain also performs more efficiently since fewer Commands are executed and the resulting Outputs are smaller.