Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 5

Parsing Nested JSON with the JSON Parse Tool

Recommended resources

Hello! Welcome to your fifth lesson in the Alteryx Advanced Certification course.

In our last lesson, we mastered using the Download tool to make authenticated GET requests and pull raw data from APIs. Today, we'll address the critical next step: making sense of that data. API responses are very often formatted in JSON (JavaScript Object Notation), a powerful but nested format that isn't immediately usable in Alteryx's row-and-column world.

This lesson focuses on the learning outcome: Parse nested JSON data using the JSON Parse tool. We will learn how to take a complex, hierarchical JSON string and transform it into a clean, flat, tabular structure ready for analysis. This is a fundamental skill for anyone working with modern data sources.

1. What is JSON and Why Do We Need to Parse It?

Before we dive into the tool, let's quickly establish what JSON looks like and what "parsing" actually means in this context. JSON data is built on two main structures:

  • Objects: Collections of key-value pairs, enclosed in curly braces {}. For example: {"name": "John", "age": 30}.
  • Arrays: Ordered lists of values, enclosed in square brackets []. For example: ["Alteryx", "Tableau", "SQL"].

These structures can be nested inside each other to represent complex data, like a customer with multiple addresses, or a blog post with multiple tags.

To understand this better, please read the first section of the following article from the Alteryx Community.

JSON Parse Tool in Alteryx 2024.2

This article explains the basic structure of JSON with a clear example and a helpful tree diagram that visualizes the nesting.

Please read section '1. JSON Structure'. Focus on understanding the difference between an object ({}) and an array ([]), and how they can be nested. The tree diagram in 'Fig. 2' is an excellent visualization of this concept.

The goal of parsing is to "un-nest" or flatten this structure into a standard table. The image below shows this transformation perfectly. The nested JSON on the left becomes the flat table on the right, with columns like JSON_Name showing the path to the data point and JSON_ValueString holding the actual value.

JSON to Tabular Data Transformation
This image illustrates the core function of the JSON Parse tool: converting a hierarchical JSON structure into a flat table with `JSON_Name` and `JSON_ValueString` columns.

2. The Classic Parsing Method

For many years, the standard way to parse JSON in Alteryx involved a multi-tool process. While newer methods exist (which we'll cover next), this "classic" approach is fundamental to understand as you will encounter it in many existing workflows.

The process typically involves:

  1. JSON Parse Tool: To do the initial flattening into name/value pairs.
  2. Text to Columns Tool: To split the JSON_Name path into separate fields.
  3. Cross Tab Tool: To pivot the data from a long format to a wide, tabular format.

The following video provides a comprehensive walkthrough of this entire process.

Alteryx - How to Parse a JSON File

This video by Nathan Patrick Taylor demonstrates the complete classic workflow, from initial parsing to the final reshaping of the data.

Watch from 03:58 to 11:03. This is a detailed section, so pay close attention to the role of each tool: JSON Parse (03:58): See how it's configured and what its initial output looks like. Text to Columns (04:54): Understand how it's used to break down the JSON_Name field. Filter & Select (07:30): Observe how the data is cleaned up before pivoting. Cross Tab (09:40): This is the key step. See how it uses a record identifier, column headers, and values to create the final table.

This method is robust and gives you fine-grained control, but it can become cumbersome with deeply nested JSON.

3. The Modern Method: AMP Engine Features

With the introduction of the Alteryx Multi-threaded Processing (AMP) Engine, the JSON Parse tool gained powerful new capabilities that can significantly simplify this process. Instead of the multi-tool chain, you can often parse complex JSON by chaining a few JSON Parse tools together, each configured to peel back one layer of the structure.

The key options, which require the AMP engine to be enabled, are:

  • Unnest JSON field (AMP Only): Use this when the data in a field is a JSON object ({...}).
  • Flatten array (AMP Only): Use this when the data in a field is a JSON array ([...]).

The Alteryx Community article you reviewed earlier provides an excellent, detailed guide on using these modern features. We will now study its main section.

JSON Parse Tool in Alteryx 2024.2

This is the core of our lesson. This guide demonstrates how to use the new AMP-only features to parse a complex API response step-by-step.

Please read section '2. Flatten Nested Objects and Nested Arrays with the JSON Parse Tool' carefully. Follow the logic from one step to the next: First Parse (Unnest): Notice how it unnests the top-level object. Second Parse (Flatten): See how it takes the results field (which is an array) and flattens it, creating multiple rows. Third Parse (Unnest): The process is repeated on the newly flattened objects. Splitting the branch: This is a critical advanced technique. Pay close attention to why the workflow is split into two branches to handle the residents and films arrays separately before being joined back together. This prevents an incorrect duplication of data (a Cartesian join).

This method is generally more efficient and easier to debug for complex JSON structures than the classic approach. Mastering the concept of chaining parse tools and splitting branches is a key skill for the Advanced certification.

4. Other Parsing Scenarios

While our focus has been on parsing API data from the Download tool, remember that the JSON Parse tool can handle any valid JSON string in your workflow.

Parsing from a Local File: If your JSON data is in a local .json or .txt file, you can't use the standard Input Data tool directly. Instead, you would use a workflow like Blob Input -> Blob Convert -> JSON Parse. The Blob Convert tool is used to read the entire file's content into a single text field, which the JSON Parse tool can then process. The first part of the video by Nathan Patrick Taylor (resource LINK, from 02:08 to 03:58) demonstrates this exact process.

Different Output Options: The JSON Parse tool also has legacy options to "Output values into data type specific fields" or a "single string field." The modern AMP features have largely superseded these, but it's good to know they exist. Sections 3 and 4 of the "JSON Parse Tool in Alteryx 2024.2" article (resource LINK) briefly describe them.

Conclusion

You now have a robust understanding of how to handle one of the most common data structures you'll encounter in modern analytics. By transforming nested JSON into a usable tabular format, you've unlocked the ability to fully integrate API data into your Alteryx workflows.

Key Takeaways:

  • The JSON Parse tool is essential for converting nested JSON objects and arrays into a flat table structure.
  • The classic method involves a sequence of JSON Parse, Text to Columns, and Cross Tab tools to reshape the data.
  • The modern AMP Engine method uses the Unnest JSON field and Flatten Array options, often by chaining multiple JSON Parse tools, which is more direct and efficient.
  • For objects containing multiple arrays, use the "split branch" technique: parse each array in a separate stream and then join the results back on a common key to avoid data duplication.

In our next lesson, we will move on to another structured data format you'll often encounter: XML. We'll learn to use the XML Parse tool, and you'll see many conceptual parallels to the JSON parsing techniques we've covered today.