4.2 Complex Data Types: Arrays, Structs, and JSON

Complex Data Types: Arrays, Structs, and JSON

Core Focus: Cloud-native analytical data warehouses achieve peak performance by avoiding traditional third-normal-form relational designs. By utilizing BigQuery STRUCT, ARRAY, and native JSON types, data practitioners co-locate related records on disk, minimizing distributed network shuffle operations while preserving clean hierarchical relationships.

In classical relational database systems (such as PostgreSQL or MySQL), schema normalization (3NF) is considered a best practice to avoid redundancy. In a normalized model, an e-commerce platform stores orders in an orders table and individual items in an order_items table linked by a foreign key. At petabyte scale in a distributed cloud warehouse, joining these two tables requires shuffling gigabytes of data across compute nodes over the network. BigQuery solves this architectural bottleneck through nested and repeated data structures.

---

Denormalized Schema Design: The Colocated Advantage

BigQuery's underlying columnar storage engine, Capacitor, organizes table data into independent columnar files. When data is modeled using nested and repeated fields:

• Parent-Child Colocation: The parent record (e.g., order_id, customer_id, order_date) and all associated child items (e.g., item_id, quantity, price) are physically stored together in the same underlying columnar block.
• Elimination of Shuffle Joins: Aggregations that combine parent metadata with line-item details can be computed locally within a single worker slot without sending data across the network backplane.
• Reduced Storage Costs and I/O: Capacitor stores leaf elements of nested structures in separate columnar files with repetition and definition levels (based on Google's Dremel paper). Queries scanning only parent columns read zero bytes from the child columns, maximizing query cost efficiency.

Relational 3NF (Normalized):        BigQuery Denormalized (Nested & Repeated):
[Orders Table]                      [Orders Table]
  order_id                           order_id
  customer_id                        customer_id
       | (Requires Shuffle Join)      line_items: ARRAY<STRUCT<
[Order_Items Table]                                item_id STRING,
  order_id (FK)                                     quantity INT64,
  item_id                                           price NUMERIC
  quantity                                        >>
  price

---

The STRUCT Data Type (Nested Records)

A STRUCT (or RECORD) is an ordered collection of named and strongly typed fields. It represents a 1:1 nested relationship, grouping logically related attributes into a single composite entity.

Characteristics of STRUCT

• Field Typing: Each field within a STRUCT has an explicit name and data type (e.g., STRUCT<street STRING, city STRING, postal_code INT64>).
• Direct Dot-Notation: Sub-fields within a struct can be accessed directly using standard dot-notation (address.city).
• Anonymous Structs: Structs can be defined anonymously using tuple syntax (value1, value2) or explicitly using STRUCT(value1 AS field1, value2 AS field2).

-- Creating and querying a table with STRUCT fields
WITH Customers AS (
  SELECT
    101 AS customer_id,
    'Elena Rostova' AS customer_name,
    STRUCT(
      '100 Market St' AS street,
      'San Francisco' AS city,
      'CA' AS state,
      94105 AS postal_code
    ) AS address
)
SELECT
  customer_id,
  customer_name,
  address.city,
  address.postal_code
FROM Customers;

---

The ARRAY Data Type (Repeated Fields)

An ARRAY is an ordered list of zero or more elements of the exact same data type. It represents a 1:N repeated relationship.

Rules and Constraints for Arrays

1. Uniform Data Type: All elements in an array must share the same type (e.g., ARRAY<INT64>, ARRAY<STRING>).
2. No Direct Arrays of Arrays: BigQuery does not permit an array to directly contain another array (ARRAY<ARRAY<T>> is invalid syntax). To model multi-dimensional structures, you must wrap the inner array inside a STRUCT (e.g., ARRAY<STRUCT<nested_list ARRAY<INT64>>>).
3. Array Creation: Arrays can be constructed using bracket notation ['red', 'green', 'blue'] or generated via the ARRAY() constructor.
4. Aggregation into Arrays: The ARRAY_AGG() aggregate function collects values across grouped rows into a single array ordered by an optional ORDER BY clause.

-- Aggregating customer tags into an array
SELECT
  customer_id,
  ARRAY_AGG(tag_name ORDER BY tag_priority DESC) AS customer_tags
FROM `marketing.user_tags`
GROUP BY customer_id;

---

Combining Types: ARRAY<STRUCT<...>> (Repeated Records)

The most prevalent schema pattern in enterprise BigQuery implementations combines arrays and structs: ARRAY<STRUCT<...>>. This construct represents an array where each element is a composite struct, effectively embedding an entire child table inside each parent row.

-- Declaring and querying an order with repeated line items
WITH Orders AS (
  SELECT
    'ORD-2026-9901' AS order_id,
    CURRENT_DATE() AS order_date,
    [
      STRUCT('SKU-A' AS sku, 2 AS qty, 19.99 AS unit_price),
      STRUCT('SKU-B' AS sku, 1 AS qty, 49.50 AS unit_price),
      STRUCT('SKU-C' AS sku, 5 AS qty, 4.25 AS unit_price)
    ] AS line_items
)
SELECT
  order_id,
  order_date,
  line_items
FROM Orders;

---

Flattening Repeated Records: The UNNEST Operator

Because an ARRAY represents multiple values stored within a single row, standard relational operators (such as WHERE item.price > 20) cannot directly inspect individual array items across rows without expanding them. The UNNEST operator takes an array as input and flattens it into a relational table format, generating a row for each element in the array.

CROSS JOIN UNNEST vs. LEFT JOIN UNNEST

When unnesting an array in BigQuery, the join type determines how rows with empty or null arrays are handled:

1. CROSS JOIN UNNEST (or comma syntax , UNNEST(...)):
   • Pairs each parent row with every element in its array.
   • Critical Risk: If a parent row has an empty array ([]) or a NULL array, the cross join evaluates to zero rows, completely dropping the parent record from the output.
2. LEFT JOIN UNNEST(...) ON TRUE:
   • Preserves all parent rows regardless of array contents.
   • If the array is empty or NULL, the parent row is still emitted, with all unnested child fields populated as NULL.

-- Preserving all orders, even those with zero line items (e.g., cancelled or pending)
SELECT
  o.order_id,
  o.order_date,
  item.sku,
  item.qty,
  item.unit_price
FROM `ecommerce.orders` AS o
LEFT JOIN UNNEST(o.line_items) AS item ON TRUE;

---

Querying Arrays Without Unnesting

In many analytical scenarios, unnesting an array is unnecessary and computationally wasteful. BigQuery provides built-in array functions and scalar expressions to inspect arrays in place:

1. Array Length

ARRAY_LENGTH(array_expression) returns the number of elements in an array (or NULL if the array is null):

SELECT order_id, ARRAY_LENGTH(line_items) AS total_distinct_items
FROM `ecommerce.orders`;

2. Array Element Indexing: OFFSET vs. ORDINAL

BigQuery provides two distinct lookup mechanisms to access individual array elements:

• OFFSET(index): Uses zero-based indexing (0 represents the first element, 1 represents the second).
• ORDINAL(position): Uses one-based indexing (1 represents the first element, 2 represents the second).

Exam Trap: Accessing an array index that does not exist (e.g., tags[OFFSET(5)] when ARRAY_LENGTH(tags) is 2) triggers a fatal runtime error: Array index 5 out of bounds. To prevent query failure, always use the safe accessors: SAFE_OFFSET() or SAFE_ORDINAL(), which return NULL when the index is out of bounds.

SELECT
  order_id,
  -- Retrieves first item using 0-based index safely
  line_items[SAFE_OFFSET(0)].sku AS primary_sku,
  -- Retrieves first item using 1-based index safely
  line_items[SAFE_ORDINAL(1)].unit_price AS primary_price
FROM `ecommerce.orders`;

3. Correlated Scalar Subqueries

You can aggregate array contents without expanding the outer query cardinality by executing a correlated subquery on the unnested array directly within the SELECT list:

SELECT
  order_id,
  order_date,
  (SELECT SUM(qty * unit_price) FROM UNNEST(line_items)) AS calculated_total_value
FROM `ecommerce.orders`;

---

Native Binary JSON Data Type

While STRUCT and ARRAY provide high performance for schemas with known, static structures, modern data pipelines frequently ingest semi-structured payloads (such as webhooks, IoT sensor readings, and clickstream events) with dynamic or rapidly changing schemas. BigQuery provides a native JSON data type for this purpose.

Native JSON vs. Legacy Stringified JSON

Prior to the native JSON type, engineers stored JSON documents as raw STRING columns and parsed them at runtime using functions like JSON_EXTRACT(). The native JSON type provides significant architectural advantages:

• Binary Capacitor Encoding: BigQuery parses JSON text upon ingestion into an optimized binary format. Sub-fields are indexed and compressed, allowing the query engine to read individual attributes without scanning the entire JSON document text.
• Schema-on-Read Flexibility: New attributes can be ingested immediately without running DDL schema migration scripts (ALTER TABLE).

JSON Navigation and Functions

Google Standard SQL supports both direct dot-notation navigation and dedicated extraction functions on JSON columns:

-- Demonstrating JSON extraction
WITH RawTelemetry AS (
  SELECT JSON '{
    "device_id": "DEV-4091",
    "metadata": {"firmware": "v2.4", "battery_pct": 88},
    "readings": [14.2, 14.8, 15.1]
  }' AS payload
)
SELECT
  -- JSON_VALUE extracts scalar values as SQL STRING
  JSON_VALUE(payload.device_id) AS device_id,
  CAST(JSON_VALUE(payload.metadata.battery_pct) AS INT64) AS battery_pct,
  -- JSON_QUERY extracts composite arrays or objects as JSON
  JSON_QUERY(payload.metadata) AS metadata_object,
  JSON_QUERY(payload.readings) AS readings_array,
  -- Extracting a scalar element from an array inside JSON
  CAST(JSON_VALUE(payload.readings[0]) AS FLOAT64) AS first_reading
FROM RawTelemetry;

---

Comparison: When to Use STRUCT/ARRAY vs. JSON

---

Common Exam Traps & Practitioner Scenarios

Exam Tip: Pay close attention to join conditions when unnesting. If an exam question asks how to list all customers and their purchases while ensuring customers who have never made a purchase are still included, CROSS JOIN UNNEST is incorrect; you must use LEFT JOIN UNNEST(...) ON TRUE.

Trap 1: Accidental Data Loss with CROSS JOIN UNNEST

• The Trap: An analyst writes FROM customers c, UNNEST(c.orders) o to produce a customer summary report.
• The Reality: The comma syntax is shorthand for CROSS JOIN. Any customer with zero orders (an empty array []) is silently dropped from the final output, skewing counts and retention analytics.

Trap 2: Using JSON_VALUE on an Object or Array

• The Trap: Attempting to extract an array of phone numbers from a JSON payload using JSON_VALUE(payload.phone_numbers).
• The Reality: JSON_VALUE only extracts scalar values. If the target path points to a JSON object or array, JSON_VALUE returns NULL. To extract a nested object or array, use JSON_QUERY.

Trap 3: Off-By-One Errors with OFFSET vs. ORDINAL

• The Trap: An engineer intending to extract the second element from an array uses tags[OFFSET(2)].
• The Reality: OFFSET is zero-based (OFFSET(0) is item 1; OFFSET(1) is item 2). OFFSET(2) accesses the third item. To retrieve the second item, use OFFSET(1) or ORDINAL(2).