The Product Extensibility team, of which I am a member, frequently builds tools that interact with partner platforms (e.g., Google, Salesforce, Adobe, etc.) using REST APIs. JavaScript Object Notation (JSON) is the preferred format when interacting with these APIs. Do you also interact with REST APIs? Do you format your data as JSON using multiple Alteryx Designer tools? If so, you should download the attached JSON Build tool. The JSON Build tool provides a way to quickly and easily format your data as JSON.
What it does...
The JSON Build tool has a straightforward interface; allowing you to output your data in one of two JSON formats - Records as Array or Records as Object. You can switch between them by selecting the respective radio button in the tool configuration.
The Records as Array format uses the incoming field names as a name, and the incoming records are transformed into an array and set as the value.
Figure 1: JSON Build tool interface when Records as Array is selected
The Records as Object format treats each incoming record as an object in an array.
Figure 2: JSON Build tool interface when Records as Object is selected
How it was built...
The tool uses the HTML GUI SDK for the frontend, a JavaScript engine file as the backend, and has been packaged as a .yxi file for straightforward tool distribution.
JSON BuildGui.html contains the code for the tool's interface. The interface is straightforward and uses a RadioGroup widget to handle selection between the two output methods. The RadioGroup widget is added to the interface through the <ayx> tag and the data-ui-props argument.
<fieldset>
<legend class="legendHeader">Select Output Format</legend>
<article id="widgetList">
<section>
<ayx data-ui-props='{type:"RadioGroup", widgetId:"OutputSelector"}'></ayx>
</section>
<section>
<div id="example" class="example"></div>
</section>
</article>
</fieldset>
To persist the configuration, I also added a StringSelector data item to store the radio button selection.
window.Alteryx.Gui.BeforeLoad = function (manager, AlteryxDataItems, json) {
// Creates a new data item called OutputSelector
var stringSelector = new AlteryxDataItems.StringSelector('OutputSelector', {
// Sets the RadioGroup options
optionList: [
{label: 'Records as Array', value: 'array'},
{label: 'Records as Object', value: 'object'}
]
})
// Adds the OutputSelector data item to the manager
manager.addDataItem(stringSelector)
// Binds the OutputSelector widget and data item
manager.bindDataItemToWidget(stringSelector, 'OutputSelector')
// Sets the default value of OutputSelector to Records as Array:array
manager.getDataItem('OutputSelector').setValue('array')
// Displays the output example based on selection
displayHelp(manager);
}
An example is shown in the interface to help people understand how the tool output will look - depending on the format selected. To accomplish this, I created a function, displayHelp, that manipulates the DOM. The function inserts an example for each JSON format into the DOM.
const displayHelp = (manager) => {
const objectJSON =`<i><pre>
Example:
<code>
[
{
1st Field Name: 1st Record Value,
2nd Field Name: 1st Record Value,
...
},
{
1st Field Name: 2nd Record Value,
2nd Field Name: 2nd Record Value,
...
},
...
]</code></pre></i>`
const arrayJSON = `<i><pre>
Example:
<code>
{
1st Field Name: [
1st Record Value,
2nd Record Value,
...
],
2nd Field Name: [
1st Record Value,
2nd Record Value,
...
],
...
}</code></pre></i>`
switch (Alteryx.Gui.Manager.getDataItem('OutputSelector').getValue()) {
case 'array':
document.getElementById('example').innerHTML = arrayJSON
break;
case 'object':
document.getElementById('example').innerHTML = objectJSON
break;
default:
document.getElementById('example').innerHTML = ''
}
}
Alteryx.Gui.AfterLoad = (manager) => {
displayHelp(manager)
// Add listener to OutpuSelector that runs displayHelp whenever the value changes
Alteryx.Gui.Manager.getDataItem('OutputSelector').registerPropertyListener('value', displayHelp)
}
To interact with the Alteryx Engine, a JavaScript backend, JSON BuildEngine.html, was used. The tool's core functionality is contained within the buildRecordObjects and buildRecordArray functions. The other code consists of boilerplate used to facilitate engine interaction. For more on this, please check out Ned Harding's blog post.
The buildRecordObjects function takes the incoming data stream and performs two for-loops to build the JSON object and push it into an array. The array is what’s pushed through the output.
const buildRecordObjects = (data) => {
var JSONobj = JSONBuild.JSONobj = JSONBuild.JSONobj || []
for (var row in data.Records) {
var obj = {}
for (var column in data.Records[row]) {
obj[JSONBuild.outputFieldNames[column]] = data.Records[row][column]
}
JSONobj.push(obj)
}
}
There’s a similar function for Records as Array. The buildRecordArray function uses multiple for-loops to create arrays containing the fields and records. The buildJSON function transforms these names and data arrays into a JSON object that is pushed to the tool's output.
const buildRecordArray = (data) => {
var fieldArray = JSONBuild.fieldArray = JSONBuild.fieldArray || [];
for (var column in data.Records[0]) {
var dataArray = [];
for (var row in data.Records) {
dataArray.push(data.Records[row][column])
}
fieldArray.push(dataArray)
}
const buildJSON = (names, data) => {
const JSONobj = {}
for (var record in names) {
JSONobj[names[record]] = data[record]
}
return JSONobj
}
var JSONobj = JSONBuild.JSONobj = buildJSON(JSONBuild.outputFieldNames, JSONBuild.fieldArray)
}
That is a high-level view of how the tool was constructed. You can find the full source code on Github: stephen-wagner/json-build-alteryx-tool.
At this time, the JavaScript Engine SDK continues to be unsupported for the near future. If you are building a custom tool, consider using the Python SDK, C++, or a macro for the runtime of your tool. Keep your eyes on the Alteryx Developer Tools, there will be much more to come in the future in this area. Ed.
JSON Build tool is compatible with Alteryx Designer 2018.1+