Sometimes the end result of your data prepping needs to be a JSON. This can be the case when for instance you need to upload your data to a target system using an API. In this blog I will tell you what it takes to transform your data into the JSON format with the JSON Build Tool in Alteryx Designer.
JSON is a lightweight data-interchange format, meant to be easy for humans to read and write and for machines to parse and generate. Roughly speaking, it is a nestable structure which consists of key-value pairs (think of objects or records) and lists of values (think of arrays).
There are several ways to build a JSON structure with Alteryx Designer Desktop:
I prefer working with the JSON build tool. Instead of having to take care of each and every accolade or bracket, which can be rather error prone, you rather work with an intermediary definition the structure of our data, and let the Tool do the proper JSON building.
The JSON Build Tool can be found in the Laboratory category in the Tool Bar of Alteryx Designer. It needs one column for the keys, which you assign in the ‘Name Field’ dropdown. Depending on the data types, you can use multiple columns for your values:
For this blog, I have used only string values.
The JSON Build Tools expects a certain syntax in the naming of your key-value pairs. For the specific JSON you are trying to build, the easiest way of figuring this out would be using a working example from documentation (if available), and having it parsed by the JSON Parse Tool. The result from the JSON Parse Tool can immediately be used by the JSON Build Tool to rebuild the same JSON:
Once you’ve seen this naming syntax, you can start designing a workflow that preps your actual data so it conforms to that syntax.
In case you do not have a working example available, and in general:
The most basic structure is a simple name-value pair, or an array:
An array needs a dot-digit(s) suffix, so you get name.index:
Even if the array contains just one value, you need to apply this:
For nesting, you need the name.name structure:
For an array of objects, you need the name.index.name structure:
BE AWARE in which order you provide the records, as the example below might not render the expected result:
For nested arrays, you would need:
These were some basic pointers on what you need to build a JSON with the JSON Build Tool in Alteryx Designer.