9.3 Complex Transformations: Higher-Order Functions, Arrays, Structs, & JSON Parsing

9.3 Complex Transformations: Higher-Order Functions, Arrays, Structs, & JSON Parsing

DP-750 Exam Focus: Master nested and complex data transformations in Azure Databricks. Understand JSON serialization and deserialization using from_json(), to_json(), and schema_of_json(); struct navigation and manipulation (withField, dropFields); array expansions (explode vs explode_outer, posexplode); and inline array processing with Higher-Order Functions (transform, filter, exists, forall, aggregate, zip_with).

---

1. JSON Parsing & Serialization in Spark SQL / PySpark

In modern cloud architectures, events from IoT hubs, webhooks, and REST APIs arrive as JSON string payloads stored in Bronze Delta tables. Transforming these strings into queryable relational columns requires robust deserialization functions.

+-----------------------------------------------------------------------------------+
|                             JSON PARSING & SCHEMA ENGINE                          |
+-----------------------------------------------------------------------------------+
|                                                                                   |
|  1. schema_of_json(sample_string)                                                |
|     - Dynamically infers DDL schema string from a representative JSON sample.     |
|                                                                                   |
|  2. from_json(json_column, schema, [options])                                     |
|     - Parses JSON string into a structured Spark StructType or ArrayType.         |
|                                                                                   |
|  3. to_json(struct_or_map_column)                                                 |
|     - Serializes complex Struct/Map/Array columns back into compact JSON strings. |
|                                                                                   |
|  4. get_json_object(json_column, '$.path.to.field')                              |
|     - Extracts a single scalar value via JSONPath (ideal for lightweight queries).|
+-----------------------------------------------------------------------------------+

from_json() with Schema Definition

from_json() deserializes a JSON string column into a structured StructType or ArrayType. It requires an explicit schema supplied as a DDL string or StructType definition:

-- SQL: Parsing JSON with DDL schema
SELECT 
    event_id,
    from_json(raw_json, 'customer_id BIGINT, items ARRAY<STRUCT<item_id: STRING, price: DOUBLE, qty: INT>>, purchase_time TIMESTAMP') AS parsed_data
FROM bronze.web_events;

# PySpark: Parsing JSON with StructType schema
from pyspark.sql.functions import from_json, col
from pyspark.sql.types import StructType, StructField, StringType, DoubleType, IntegerType, ArrayType

item_schema = StructType([
    StructField("item_id", StringType(), True),
    StructField("price", DoubleType(), True),
    StructField("qty", IntegerType(), True)
])

payload_schema = StructType([
    StructField("customer_id", StringType(), True),
    StructField("items", ArrayType(item_schema), True)
])

df_parsed = df_bronze.withColumn("data", from_json(col("raw_payload"), payload_schema))

Dynamic Schema Derivation with schema_of_json()

When the exact schema of a complex JSON string is unknown during development, schema_of_json() can inspect a sample string literal and generate the exact DDL schema definition:

-- Dynamically derive the schema DDL string
SELECT schema_of_json('{"order_id": 101, "total": 99.50, "tags": ["sale", "online"]}');
-- Result: STRUCT<order_id: BIGINT, tags: ARRAY<STRING>, total: DECIMAL(3,1)>

---

2. Struct Operations & Hierarchical Data Modeling

StructType columns represent complex hierarchical objects containing nested fields. Databricks provides powerful operators to construct, access, and modify structs without flattening tables.

Accessing and Expanding Struct Fields

• Dot Notation: Access child attributes directly via parent_struct.child_field.
• Star Expansion (.*): Expand all subfields of a struct into top-level columns.

-- Access nested attributes
SELECT 
    parsed_data.customer_id,
    parsed_data.purchase_time,
    parsed_data.items[0].price AS first_item_price
FROM silver.parsed_events;

-- Expand all fields of a struct into top-level columns
SELECT parsed_data.* FROM silver.parsed_events;

Struct Construction & Modification (withField, dropFields)

# PySpark: Struct manipulation with withField and dropFields
from pyspark.sql.functions import col, struct, lit

# 1. Create a new struct from existing columns
df_nested = df.withColumn("address_info", struct(col("street"), col("city"), col("zip_code")))

# 2. Add or update a nested field inside an existing struct
df_updated = df_nested.withColumn("address_info", col("address_info").withField("country", lit("USA")))

# 3. Drop a nested field from an existing struct
df_dropped = df_updated.withColumn("address_info", col("address_info").dropFields("zip_code"))

---

3. Array Operations: Expansion & Normalization

When nested arrays need to be unpacked into relational rows for traditional BI joins or aggregation, Spark provides array expansion functions.

                         ARRAY EXPANSION FUNCTION COMPARISON

  Original Row: [ ID: 1, Items: ['Apple', 'Banana'] ]
  Original Row: [ ID: 2, Items: [] / NULL ]

  1. explode(items)
     -> ID: 1, Item: Apple
     -> ID: 1, Item: Banana
     (Row 2 is DROPPED entirely because array was empty/null)

  2. explode_outer(items)
     -> ID: 1, Item: Apple
     -> ID: 1, Item: Banana
     -> ID: 2, Item: NULL  <-- Preserves parent row

  3. posexplode(items)
     -> ID: 1, Pos: 0, Item: Apple
     -> ID: 1, Pos: 1, Item: Banana

---

4. Higher-Order Functions: In-Memory Array Processing

Historically, modifying or filtering elements inside an array required a 3-step anti-pattern:

1. explode() the array into multiple individual rows.
2. Apply the transformation or filter expression.
3. groupBy() on the original primary key and re-collect using collect_list().

The Problem with Explode-GroupBy

The explode-groupBy pattern is computationally catastrophic for big data: it duplicates parent columns, multiplies row volume, forces an expensive distributed shuffle across network boundaries during groupBy(), and risks Out-Of-Memory (OOM) errors during collect_list().

The Higher-Order Functions Solution

Higher-Order Functions operate directly on arrays inline using anonymous lambda expressions (x -> expression). They execute within a single Spark task in-memory, eliminating distributed shuffles entirely and delivering a 2x to 5x performance acceleration.

+---------------------------------------------------------------------------------------+
|                HIGHER-ORDER FUNCTIONS VS. EXPLODE-GROUPBY ARCHITECTURE                |
+---------------------------------------------------------------------------------------+
|                                                                                       |
|  TRADITIONAL PATTERN (EXPENSIVE):                                                     |
|  [ Array Row ] ---> explode() ---> [ Multi-Rows ] ---> groupBy() [SHUFFLE] ---> Array |
|                                                                                       |
|  HIGHER-ORDER PATTERN (ZERO SHUFFLE / IN-MEMORY):                                     |
|  [ Array Row ] ===> transform(items, x -> x * 1.1) ===> [ Modified Array Row ]        |
+---------------------------------------------------------------------------------------+

---

5. Comprehensive Higher-Order Function Reference

1. transform(): Element-wise Mapping

Applies a transformation expression to every element in an array, returning a new array with modified elements.

-- Apply 10% tax to all item prices in an array
SELECT 
    order_id,
    transform(item_prices, price -> ROUND(price * 1.10, 2)) AS prices_with_tax
FROM silver.orders;

2. filter(): Array Element Filtering

Evaluates a boolean predicate on each element, returning an array containing only elements that evaluate to TRUE.

-- Retain only items where price > 50.00
SELECT 
    order_id,
    filter(items, item -> item.price > 50.0) AS premium_items
FROM silver.orders;

3. exists(): Existential Quantifier

Returns TRUE if at least one element in the array satisfies the boolean predicate; otherwise returns FALSE.

-- Identify orders containing any discounted item
SELECT 
    order_id,
    exists(items, item -> item.is_discounted = TRUE) AS has_discount
FROM silver.orders;

4. forall(): Universal Quantifier

Returns TRUE if every single element in the array satisfies the boolean predicate.

-- Check if all items in the order are in stock
SELECT 
    order_id,
    forall(items, item -> item.in_stock = TRUE) AS all_in_stock
FROM silver.orders;

5. aggregate(): Array Reduction to Scalar

Reduces all elements in an array to a single scalar value using an accumulator and an optional finisher expression.

-- Syntax: aggregate(array, initial_value, (acc, x) -> merge_expr, acc -> finisher_expr)
-- Calculate total order amount across all items (price * quantity)
SELECT 
    order_id,
    aggregate(
        items, 
        0.0, 
        (acc, item) -> acc + (item.price * item.qty),
        acc -> ROUND(acc, 2)
    ) AS total_order_amount
FROM silver.orders;

6. zip_with(): Pairwise Array Merging

Combines two equal-sized arrays into a single array by applying a binary lambda function across corresponding element indices.

-- Multiply quantities by unit costs across two arrays
SELECT 
    order_id,
    zip_with(quantities, unit_prices, (qty, price) -> qty * price) AS line_item_totals
FROM silver.inventory;