How to Parse JSON in DQ+

Parsing JSON in DQ+ can be achieved via the out-of-the-box JavaScript node. This article will provide an overview of the JavaScript code required to parse JSON into DQ+ data fields. This article assumes that you have a basic understanding of both JavaScript and JSON structures, as different JSON structures will require tweaks to the JavaScript code.

Receiving JSON data in DQ+

The most common way to receive JSON data in DQ+ is via the REST API node, typically for the use case of integrating with Data3Sixty Govern. JSON data can also be stored in a database column and inputted via a Data Store. 

In this example, JSON data is already captured within DQ+ via a REST API node:

The output from the REST API node is the following:

It is recommended to add additional checks after receiving a REST API result to check the status code. However, since the focus of this article is parsing JSON, it will be assumed that the result comes back successfully. 

Parsing JSON data with the JavaScript Node

First, select your JavaScript node and add Output fields based upon the data getting parsed from JSON. After the fields are added, click the Edit Script button to open the JavaScript editor.

For reference, the completed block of code for this example is below. Some of the code is DQ+ specific and not part of JavaScript's standard libraries. We'll walk through each portion of the code beneath the screenshot.

DQ+ automatically assigns the current input record's data to an "input" variable. Each column of data is stored as an element within this variable. For example, the JSON data is in the "body" column, so "input.body" will refer to the highlighted data below:

This record's data to then fed into JavaScript's standard JSON parse method, and the output is stored into an internal variable named jsonObject. 

var jsonObject = JSON.parse(input.body);

Next, we will cycle through all entries within the "items" key to extract its data.

This is done via a standard for loop:

for (var i = 0; i < jsonObject.items.length; i++) {
  ...
}

Within the for loop, we will assign output values based upon the related JSON fields. DQ+ automatically creates an "output" variable, which contains elements for each field specified as outputs (created in the first step of this section). The code below maps the input fields to output fields. JSON treats dates as strings, so an extra conversation is required to convert the strings back to dates.

 output.AssetId = jsonObject.items[i].AssetId;
 output.AssetUid = jsonObject.items[i].AssetUid.toUpperCase();
 output.AssetTypeId = jsonObject.items[i].AssetTypeId;
 output.AssetTypeUid = jsonObject.items[i].AssetTypeUid.toUpperCase();
 output.UpdatedOn = new Date(jsonObject.items[i].UpdatedOn);
 output.CreatedOn = new Date(jsonObject.items[i].CreatedOn);
 output.Name = jsonObject.items[i].Name;

The last line of code within the for loop is the emitRecord method. This is a DQ+ specific method that will take the current values assigned to the output variable and push them as an output record. Since this method is called within the for loop, there will be one output record per item in the items array within jsonObject. 

 emitRecord();

The full code from this example is below:

var jsonObject = JSON.parse(input.body);
for (var i = 0; i < jsonObject.items.length; i++) {
  output.AssetId = jsonObject.items[i].AssetId;
  output.AssetUid = jsonObject.items[i].AssetUid.toUpperCase();
  output.AssetTypeId = jsonObject.items[i].AssetTypeId;
  output.AssetTypeUid = jsonObject.items[i].AssetTypeUid.toUpperCase();
  output.UpdatedOn = new Date(jsonObject.items[i].UpdatedOn);
  output.CreatedOn = new Date(jsonObject.items[i].CreatedOn);
  output.Name = jsonObject.items[i].Name;
  emitRecord();
}

The output of the JavaScript node will now contain the JSON data in a tabular format: