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.
What is JSON?
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).
Building JSONs in Alteryx Designer
There are several ways to build a JSON structure with Alteryx Designer Desktop:
- Using the built-in JSON Build Tool. This means you have to prep the data into a form that the JSON Build Tool understands, so it can build the proper structure.
- Using a combination of transformation tools to literally build the JSON from scratch. Think of Formula, Summarize, Transpose, Select, etc.
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.
Introducing the JSON Build Tool
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.
Naming your keys
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:
Basic
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:
Nesting
For nesting, you need the name.name structure:
Array of objects
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:
Nested arrays
For nested arrays, you would need:
Conclusion
These were some basic pointers on what you need to build a JSON with the JSON Build Tool in Alteryx Designer.