Policy change: I’ve tried to avoid using AI translation as much as possible until now, but it’s become too labor-intensive, so I’ll start relying on AI translation.
Do you know JSON Parse tool in Alteryx? This tool is to transformation from JSON(JavaScript Object Notation) to structured table. Since the Designer 2024.2, some options were added, so I would like to introduce these new options.
Original JSON Parse tool option
The options of existing JSON tools have worked by breaking down each value into a directory structure starting from the top level. For example, consider the following JSON file:
[
{"name": "Goto", "instrument": "Guitar"},
{"name": "Kita", "instrument": "Guitar"},
{"name": "Ijichi", "instrument": "Drum"},
{"name": "Yamada", "instrument": "Base"}
]When this is broken down, it was parsed by separating it by data type, or by separating it into a single column, as follows:
Outputting the value to a single string field:
Output values to a single string field:
Output values to fields specific to each data type:
* In this sample, all values are strings, so everything is expanded as “JSON_ValueString.”
It is a little cumbersome to reshape this result into an easy-to-use tabular format. For example, you can convert it into table-form data with a workflow like the one below.
The result looks like this:
New Options in the JSON Parse Tool
In addition to these, Designer 2024.2 introduced new options to the JSON Parse tool.
If these options are not used in the appropriate context, they will result in an error as shown below.
So, in what cases can they be used?
* Please note that all of these are available only when the AMP Engine is enabled.
Flattening array
Arrays in JSON files are represented by square brackets []. In this case, because the content is enclosed in [] at the beginning and end, it is an array.
In other words, in the first sample there are four objects enclosed in {} within the [] array. The “Flatten Array” feature breaks this array apart and stores each object as a separate record.
Returning to the first sample, if you apply “Flatten Array” in the JSON Parse tool, you get data like the following.
You can see that each {} object becomes an individual record, and each one is assigned an index number.
At first glance, this may seem very simple, and you might wonder whether you could just remove the first and last brackets and then split the rows with a text-to-columns tool while ignoring commas inside {}. However, because the text-to-columns tool has no feature to ignore commas inside {}, that approach does not work well.
Unnest JSON field
Unnest splits flattened JSON objects into a tabular format.
If you specify the field created by flattening in Unnest, you get the following result.
Each item has been successfully separated into its own field.
About the Input Data Tool Options
The following options are also available in the Input Data tool when loading JSON files.
- Read Data in structured form
- Parse Value as String
When using the upper option, “Read Data in structured form,” the tool automatically performs the equivalent of the options explained here (Unnest and Flatten), but only when AMP is enabled.
一方、AMPエンジン利用時、「値を文字列として解析する」オプションを使うと、以下のような感じになります。
On the other hand, when using the AMP Engine, selecting the option “Parse Value as String” produces a result like the following.
If you do not use this option, the data is split into separate fields according to its data type.
Conclusion
- For simple JSON structures, the standard value output options are often sufficient, but as the structure becomes more complex, it can be useful to break it down step by step using Unnest and Flatten.
- Please note that it works only when the AMP Engine is enabled.
- This was also helpful for Day 12 of Advent of Code 2015…
Sample Workflow Download
Next blog will be…
I expect the next post will also be about tips for the Interactive Chart tool.
コメント