How to Create JSON in DQ+

Creating 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 creating different JSON structures will require tweaks to the JavaScript code.

 

Creating JSON record-by-record

To start, data in this example is in a standard tabular format outputted from the "Validation Check" node below. The input can come from any previous node. 

This particular node outputs the following data:

 

This data is then read into the a JavaScript node ("Convert individual records to JSON"):

This node has a single output field named JSONresponse, which will be later referenced within the JavaScript code:

 

After clicking the Edit Script button within the JavaScript node, the JavaScript editor will appear.  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, one of the input columns is named AssetUid, so "input.AssetUid" will refer to the highlighted data below:

 

The first line of JavaScript code creates a JSON object, mapping the appropriate key names and values from the input data:

var s = { Result: input.Result, AssetUid: input.AssetUid, MetricAssetUid: input.MetricUid };

 

Next, we call JavaScript's native JSON.stringify function to create a string representation of our JSON object. More details about this method can be found here.

var jsonOutput = JSON.stringify(s);

 

Lastly, we 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 an earlier step of this section). The code below maps our string-equivalent of our JSON object to our output field:

output.JSONresponse = jsonOutput;

 

For reference, the full script code is below:

var s = { Result: input.Result, AssetUid: input.AssetUid, MetricAssetUid: input.MetricUid };
var jsonOutput = JSON.stringify(s);
output.JSONresponse = jsonOutput;

 

The node will now output each record as a single string of JSON:

 

Depending on your use case, this is all that's required. However, if you're interested in consolidating all records in a single array of JSON, continue with the next section.

 

(Optional) Combine JSON records into a single JSON array

This example will continue where the previous example left off. The concept behind grouping the records is the following:

1. Add a new column containing a static value 
2. Add a group by node based on the new column
3. Since the column used for the group by contains a static value, all records will be grouped into a single array
4. Utilize JavaScript to ensure the array is JSON-compliant

To begin, we will continue working with the same node:

While viewing the results of the node above, the steps below, which are also shown in the screenshot below, will add a new field with a static value:

1. Add a new column
2. Rename the column (optional)
3. Specify a formula with a static result, such as a single number (1, for this example)

 

The next node is a Group node:

Within the properties of the Group node, add any name for the output field name. Make note of the name, as we'll use it later for the next JavaScript node. Then, specify a field to group by, which will be the static formula field added in a previous step. 

 

The output of the group node will look like the output below. The table symbol in the data section means that there's an array stored within this field. 

 

You can click on the table icon to view the data stored in the array:

 

The final step is to take the data in the array and make the final JSON array. This is completed via a JavaScript node:

 

Similar to the previous JavaScript node, an output field is specified:

 

Within the script editor, the following script it used:

 

The script uses the same DQ+ input and output variables as before, along with standard Javascript code. Make sure to match the field name that was specified as the Group node output (groupedRecords in the example below):

var s=[];
for (var i=0;i<input.groupedRecords.length;i++) {
 s=s.concat(input.groupedRecords[i].JSONresponse);
}
output.FullJSON="["+s+"]";

 

The final result is a single record with a string-representation of the original JSON array: