Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
StephenW
Alteryx Alumni (Retired)

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 selectedFigure 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 selectedFigure 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+

Stephen Wagner
Product Manager, Technology Alliances

Stephen Wagner is a Product Manager, Technology Alliances with a passion for enabling users to take full advantage of the Alteryx platform. Stephen’s background includes analyzing sales, marketing, and operational data for a Fortune 50 retailer, as well as data visualization development as an Analytics Consultant.

Stephen Wagner is a Product Manager, Technology Alliances with a passion for enabling users to take full advantage of the Alteryx platform. Stephen’s background includes analyzing sales, marketing, and operational data for a Fortune 50 retailer, as well as data visualization development as an Analytics Consultant.

Comments
jamielaird
14 - Magnetar

Thanks for writing this up @StephenW.  My mind is constantly blown by the scope of what can be done with Alteryx. 

 

How long did this take you to develop?

StephenW
Alteryx Alumni (Retired)

Thanks @jamielaird

 

I built it as part of our internal Innovation Days.  It took the better part of a day - most of that spent learning how to use a JavaScript engine backend.

madhukarm
7 - Meteor

@StephenW I'm using the Json build tool , working fine for me . only issue I have large data . I need to build json in batchs and post it in salesforce marketing cloud . I'm trying to find solution to my workflow for sending 50 rows per call . I have  database - getting data from - building json from the tables data  and posting on rest api call .  but data from this tables is huge 50 Mb where as my rest api accepts only 5 mb per call . I'm using throttle tool per minute 50 records before Json build, when I'm using tool i''m not able connect my download tool at the same time for posting data . My workflow waiting for throttle to complete all the records and then download tool starts post .due which Json data size is same when it is hitting download tool for posting .I have also tired waitasecond micro ,for that also it is happening same . Any suggestion will really appreciated 

StephenW
Alteryx Alumni (Retired)

@madhukarm 

 

You could try putting the Download tool into a batch macro that would take a 50 row JSON dataset one at a time.

madhukarm
7 - Meteor

1.6.PNGthis is my workflow , where Can I add batch marco to download tool . Pay load from the josn build is single input to download tool . how I add can batch marco when data from json build is one string .

 

 

StephenW
Alteryx Alumni (Retired)

@madhukarm 

 

Instead of creating one JSON object with all the data, you'll need to find a way to break it up so that you'd have a JSON object for every 50 rows of data.

davidhenington
10 - Fireball

Just digging into this, and while I see some configuration tricks i'll still need to do on my own (for my use case), this is fantastic. 

 

I love Alteryx!!!

davidhenington
10 - Fireball

so how to handle a use case where some records need to be objects, while others are an array? 

NeilR
Alteryx Alumni (Retired)

@davidhenington split records ahead of the json tool, use 2 json tools?

davidhenington
10 - Fireball

yep, pretty straight forward, thanks Neil. 

davidhenington
10 - Fireball

i do not get the "array/object" radio button mentioned in the article, but i'm using the tool that was already in my designer install. Perhaps i have to get a different version? 

 

follow up: yep, had to download and install the file from this blog post. It is not included in standard designer tool set, which is a little confusing, since JSON build is included in Laboratory section. 

StephenW
Alteryx Alumni (Retired)

Yes, @davidhenington  you are using the version that ships with Designer (which is not what the blog post refers to). Icreated this version as a poc update to that one. Download the attachment from this blog post and install it.

davidhenington
10 - Fireball

haha thanks @StephenW added my follow up in case others are confused. It is Friday and it is me though!

John-K
5 - Atom

Hi @StephenW - would you happen to have any insight as to why I might be getting this error?

 

Record #1: Tool #27: Error: Unable to resolve plugin HTML 'JSON Build\JSON BuildEngine.html'


For context, I've added the JSON Build Tool to a macro associated linked to a workflow that I've then tried loading to the Alteryx server. When I try to run it on the server, I'm then greeted with that error message after the attempted run. 

 

For what it's worth, the tool has been a lifesaver 🙂 !  Now I'm just trying to figure out how I can utilize it on the server and fully automate some tasks. 

 

Thanks!

StephenW
Alteryx Alumni (Retired)

Hi @John-K 

 

Great to hear that you've found the tool useful! What that error is indicating is that the workflow can't find the source files for the tool. Since this is happening on Server, I'd guess that the tool needs to be installed on Server. If it is installed on Server, it's likely installed within C:\Users\<user>\AppData\Roaming\Alteryx\Tools\JSON Build (which is the default YXI install location). The issue may be that the <user> that's running the workflow on Server isn't the same <user> of the installation directory. Try running the workflow as the <user> that installed the tool.

MRod
8 - Asteroid

Really needed this functionality for a use case and was ecstatic to see this write-up with your name . Hope you are doing well, @StephenW, and thank you!