Part 170: Google BigQuery Complex Types: ARRAY, STRUCT & UNNEST()

Denormalizing one-to-many relationships directly into high-speed columnar records.

1. Why Nested & Repeated Fields?

In traditional relational databases, an order with 5 items requires two tables (orders and order_items) joined by foreign keys. - Joins across petabytes are computationally expensive. - BigQuery solves this by embedding child records inside the parent record as STRUCT (nested) and ARRAY (repeated) fields!

2. Schema Definition with Nested Types

CREATE TABLE `project.ecommerce.orders` (
    order_id STRING,
    order_date DATE,
    customer STRUCT<id INT64, name STRING, email STRING>, -- Nested
    items ARRAY<STRUCT<product_id STRING, price NUMERIC, quantity INT64>> -- Repeated Nested
);

3. Flattening with UNNEST()

To filter or aggregate elements inside an ARRAY, use UNNEST():

-- Query each item row alongside its parent order
SELECT 
    order_id,
    order_date,
    customer.name AS customer_name,
    item.product_id,
    item.price * item.quantity AS item_total
FROM `project.ecommerce.orders`,
UNNEST(items) AS item
WHERE item.price > 50;

4. Aggregating into Arrays (ARRAY_AGG)

-- Collapse multiple rows into a single array
SELECT 
    customer_id,
    COUNT(order_id) AS total_orders,
    ARRAY_AGG(order_id ORDER BY order_date DESC LIMIT 5) AS recent_order_ids
FROM `project.ecommerce.orders`
GROUP BY customer_id;

Key Takeaways

• Nested and repeated fields keep related data physically collocated on disk, eliminating join overhead in analytical workloads.

In Part 171, we explore DuckDB & Embedded Analytical SQL: Vectorized Engine & Parquet Querying!