4.2 Complex Data Types: Arrays, Structs, and JSON

Google Cloud Associate Data Practitioner Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

About the Associate Data Practitioner Exam

2 Chapter 1: Data Processing Paradigms & Ingestion Strategies

1.1 ETL, ELT, and ETLT Transformation Paradigms 1.2 Data Transfer and Migration Tools 1.3 Data Quality, Cleaning, and Validation

3 Chapter 2: Data Formats & Storage Solution Selection

2.1 File Formats & Serialization Characteristics 2.2 Cloud Storage Buckets, Location Types & Storage Classes 2.3 Selecting Google Cloud Databases and Storage Services

4 Chapter 3: Loading Data & Modern Ingestion Architectures

3.1 Batch Loading Data into BigQuery 3.2 Streaming Ingestion & Deduplication Patterns 3.3 BigQuery External Tables, BigLake & Federated Queries 3.4 Schema Definition & Evolution for Loaded Data

5 Chapter 4: BigQuery SQL Analytics & Query Foundations

4.1 BigQuery SQL Syntax & Analytical Functions 4.2 Complex Data Types: Arrays, Structs, and JSON 4.3 Answering Business Questions with BigQuery: Worked Analyses

6 Chapter 5: BigQuery Performance Optimization & Cost Governance

5.1 Table Partitioning Strategies 5.2 Table Clustering & Data Skipping 5.3 Query Optimization, Execution Plans, and Cost Controls

7 Chapter 6: Interactive Analysis, Notebooks & Visual Presentation

6.1 Exploratory Analysis with Jupyter & Colab Enterprise 6.2 Looker Studio (Data Studio) Reporting & Dashboard Design 6.3 Enterprise BI with Looker & LookML Foundations 6.4 Building, Modifying & Sharing Looker Dashboards

8 Chapter 7: Machine Learning with BigQuery ML & Vertex AI

7.1 Planning an ML Project: BigQuery ML, AutoML, or Pretrained Models? 7.2 Creating, Training & Evaluating Models with BigQuery ML 7.3 Running Inference and Organizing Models in Model Registry 7.4 Using Pretrained Gemini Models from BigQuery with Remote Connections

9 Chapter 8: Pipeline Engines & Transformation Services

8.1 Transformation Engine Selection Matrix 8.2 Apache Beam & Cloud Dataflow Core Concepts 8.3 Stream Processing: Windowing, Watermarks, and Triggers

10 Chapter 9: Workflow Orchestration, Automation & Monitoring

9.1 SQL Pipelines with Dataform & Scheduled Queries 9.2 Workflow Orchestration with Cloud Composer & Workflows 9.3 Event-Driven Ingestion: Pub/Sub to BigQuery & Eventarc Triggers 9.4 Monitoring Pipelines: Dataflow Job UI, Cloud Logging & Cloud Monitoring

11 Chapter 10: Access Control, Governance & Data Sharing

10.1 IAM Principles & Role Hierarchies for Data Services 10.2 Cloud Storage Security & Access Controls 10.3 Fine-Grained Security: Column-Level & Row-Level Controls 10.4 Secure Data Sharing with Analytics Hub (BigQuery Sharing)

12 Chapter 11: Lifecycle Management, Reliability & Compliance

11.1 Object Lifecycle Management & Storage Tiering 11.2 High Availability & Disaster Recovery Architectures 11.3 Data Retention, Time Travel, and Point-in-Time Recovery 11.4 Encryption Key Management & Privacy Compliance

4.2 Complex Data Types: Arrays, Structs, and JSON

Key Takeaways Explain This Topic AI

BigQuery leverages denormalized schemas using nested (STRUCT) and repeated (ARRAY) fields to co-locate parent-child entities, eliminating expensive distributed shuffle joins.

A STRUCT represents a fixed 1:1 container of named, strongly typed attributes, while an ARRAY represents a 1:N ordered sequence of uniform data elements.

The UNNEST operator flattens repeated array elements into relational rows; using a LEFT JOIN UNNEST(...) ON TRUE pattern preserves parent rows when arrays are empty.

Array indexing in Google Standard SQL supports zero-based OFFSET and one-based ORDINAL lookups, with SAFE_ variants preventing out-of-bounds runtime exceptions.

The native JSON data type provides flexible schema-on-read querying for semi-structured data, utilizing JSON_VALUE for extracting scalar strings and JSON_QUERY for extracting JSON objects and arrays.

Last updated: October 2026

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

Uniform Data Type: All elements in an array must share the same type (e.g., ARRAY<INT64> , ARRAY<STRING> ).

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>>> ).

Array Creation: Arrays can be constructed using bracket notation ['red', 'green', 'blue'] or generated via the ARRAY() constructor.

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:

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.

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:

Function / Syntax Return Type Purpose and Behavior

json_col.user.name JSON Dot-notation navigation. Returns the inner element preserving JSON types. Returns SQL NULL if the key does not exist.

json_col.items[0] JSON Bracket notation for JSON arrays. Returns the element as a JSON type.

JSON_VALUE(json_expr, [path]) STRING Extracts a scalar value (string, number, boolean) and converts it to a SQL STRING . If the targeted value is an array or object, it returns NULL .

JSON_QUERY(json_expr, [path]) JSON Extracts an array or composite object from the JSON document, returning the result as a JSON type.

STRING(json_expr) STRING Converts an unquoted scalar JSON string value to a SQL string. -- 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

Dimension STRUCT & ARRAY Native JSON Data Type

Schema Enforcement Strict schema-on-write (strongly typed fields). Schema-on-read (flexible, permits arbitrary keys).

Query Performance Highest possible performance; Capacitor stores individual fields in dedicated columnar files. High performance for semi-structured data, but slower than pure columnar struct fields.

Storage Efficiency Maximum compression; minimal storage footprint. Moderate compression; key names stored with binary payload.

Schema Evolution Requires explicit ALTER TABLE ADD COLUMN statements. Completely dynamic; new keys ingested without schema changes.

Type Safety Compile-time SQL type verification. Runtime type resolution (type errors return NULL via JSON_VALUE ).

Primary Use Cases Core dimension/fact tables, e-commerce orders, standard logs. Dynamic third-party APIs, webhooks, unstructured event tracking.

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) .