Parsing and Flattening Nested JSON with EXPLODE

1. Extracting Data from JSON Strings

Often, you'll ingest data where an entire column is a JSON string. Spark SQL provides a simple and powerful syntax for querying directly into this nested data without needing to parse the entire file first.

The primary mechanism is the colon (:) operator, which lets you specify a path to the field you want to extract.

Let's explore the official Databricks documentation to see the various ways you can use this syntax.

Hands-On: Querying JSON

Let's put this into practice. In a Databricks notebook, you can create a sample table with nested JSON data, just like in the documentation you just read.

Step 1: Create Sample Data
Run this query to create a table named store_data with a single raw column containing a complex JSON string.

CREATE OR REPLACE TEMPORARY VIEW store_data AS 
SELECT '{
  "store": {
    "book": [
      {
        "category": "reference",
        "author": "Nigel Rees",
        "title": "Sayings of the Century",
        "price": 8.95
      },
      {
        "category": "fiction",
        "author": "Herman Melville",
        "title": "Moby Dick",
        "isbn": "0-553-21311-3",
        "price": 8.99
      }
    ],
    "bicycle": {
      "color": "red",
      "price": 19.95
    }
  },
  "owner": "amy"
}' AS raw;

Step 2: Extract Fields
Now, you can use the colon and dot notation to query this data.

• Extract a top-level field:

  SELECT raw:owner FROM store_data;
  

  Result: amy

• Extract a nested field:

  SELECT raw:store.bicycle.color FROM store_data;
  

  Result: red

• Extract an element from an array by its index:

  SELECT raw:store.book[0].title FROM store_data;
  

  Result: Sayings of the Century

• Extract a field from ALL elements in an array:

  SELECT raw:store.book[*].author FROM store_data;
  

  Result: ["Nigel Rees","Herman Melville"]

This last query is important. Notice that extracting a field from all array elements with [*] returns a new array containing just those extracted values. To turn these array elements into individual rows, we need our next function: EXPLODE.

2. Flattening Arrays with EXPLODE

The EXPLODE function takes a column that contains an array and creates a new row for each element in that array. It's the standard way to "flatten" or "denormalize" nested data structures.

In Spark SQL, EXPLODE is used with a LATERAL VIEW. This clause allows the query to process the generated rows from EXPLODE as if they were in a temporary table that you can then join back to.

This article provides a clear, practical example of the LATERAL VIEW explode pattern.

Deconstructing LATERAL VIEW EXPLODE

As you saw, the common pattern is to use a CTE to first explode the array and then join it back to the original data.

The syntax looks like this:

SELECT
  t1.id, -- Columns from the original table
  t2.exploded_book -- The new column from the exploded array
FROM 
  my_table AS t1
LATERAL VIEW 
  EXPLODE(t1.book_list) AS t2 (exploded_book)

Let's break it down:

• LATERAL VIEW: Applies the following generator function to each row.
• EXPLODE(t1.book_list): The generator function that creates a new row for each element in the book_list array.
• AS t2 (exploded_book): Gives an alias to the virtual table (t2) and the new column (exploded_book) created by EXPLODE.

It's common to place the LATERAL VIEW in a subquery or CTE to keep the logic clean, as shown in the resource you just read. This also helps avoid common errors when referencing the newly exploded column.

3. A Complete Workflow: Parse, Explode, and Select

Now, let's combine these concepts to perform the full workflow from a raw JSON string to a clean, flat table. We will use the store_data view we created earlier.

Our goal is to create a table with one row for each book, containing the owner and the book's title and author.

Step 1: Extract the book array
First, we select the top-level owner and extract the book array.

SELECT
  raw:owner AS owner,
  raw:store.book AS books
FROM store_data

The books column here is an array<struct>. Each element is a book object.

Step 2: Explode the array
Now, we use LATERAL VIEW EXPLODE on the books column.

SELECT
  owner,
  book_details
FROM (
  -- Inner query to extract the array first
  SELECT
    raw:owner AS owner,
    raw:store.book AS books
  FROM store_data
)
LATERAL VIEW EXPLODE(books) AS exploded_view (book_details)

The result of this query will have two rows. The owner column will be 'amy' for both, and the book_details column will be a struct containing the details of one book per row.

Step 3: Extract fields from the exploded struct
Finally, we can use dot notation on our exploded book_details column to get the final fields.

-- Final Query
SELECT
  owner,
  book_details.title,
  book_details.author,
  book_details.price
FROM (
  SELECT
    raw:owner AS owner,
    raw:store.book AS books
  FROM store_data
)
LATERAL VIEW EXPLODE(books) AS exploded_view (book_details)

Result:

We have successfully transformed a complex, nested JSON string into a clean, relational table.

4. A Note on PySpark and from_json

While the colon (:) syntax is convenient for interactive queries in SQL, there's a more explicit function called from_json that is used in both SQL and PySpark for parsing. This is especially useful when your JSON is mixed with other string data or when you need to enforce a specific schema.

The following video gives a great overview of parsing JSON, including the from_json and explode functions, from a PySpark perspective. Watching this will give you a head start for our next module.

Conclusion

Mastering the manipulation of semi-structured data is non-negotiable for a data engineer. Today you've added two of the most important tools for this job to your Spark SQL toolkit.

Key Takeaways

• Use the colon (:) and dot (.) notation in Spark SQL to quickly and easily extract fields from columns containing JSON strings.
• Access specific array elements by index (e.g., [0]) and extract from all elements using a wildcard (e.g., [*]).
• Use LATERAL VIEW EXPLODE to flatten a column of arrays, creating a new row for each element.
• Combine these techniques in a multi-step query—often using a CTE or subquery—to transform raw, nested JSON into a flat, relational table.

Next Steps

In this module, we've focused on powerful transformations within Spark SQL. In our next lesson, we will begin a new module, "Transforming Data with PySpark DataFrames." You'll learn how to perform many of the same operations we've discussed, but this time using the programmatic PySpark API, giving you even more power and flexibility in your data pipelines.