STRUCT: Embedded Objects

A struct is 1 column holding a fixed set of named, typed fields. The field names and their types live in the table schema, the same as top-level columns, so the engine knows before reading a single row that shipping_address has a city of type VARCHAR. In the SQL used here the type is spelled ROW; the idea is the one most people call a struct.

CREATE TABLE orders (
  order_id BIGINT,
  customer_id BIGINT,
  order_date DATE,
  shipping_address ROW(
    street VARCHAR,
    city VARCHAR,
    postal_code VARCHAR,
    country VARCHAR
  )
)

Order 1003 is a digital gift card and has no shipping address at all, so the whole struct is NULL. Reading a field uses dot notation, and a filter on a field works like a filter on any column:

SELECT
  order_id,
  shipping_address.city AS city
FROM orders
WHERE shipping_address.country = 'DE'

Why a struct beats 4 prefixed columns

The same 4 facts could live in shipping_street, shipping_city, shipping_postal_code and shipping_country. The struct wins on 3 counts. The grouping is in the schema, so a reader sees 1 address rather than 4 columns that happen to share a prefix. Absence is 1 check: shipping_address IS NULL says the order has no address, where the prefixed version needs 4 checks and a rule about which columns may be missing on their own. And the shape is reusable: billing_address can be declared with the same ROW(...) and compared field by field with shipping_address in a single expression.

A separate addresses table loses here too. Every order has exactly 1 shipping address, it is read whenever the order is read, and it is a snapshot that never changes after shipment. A join would cost a lookup per order and buy nothing, since none of the 5 questions from the previous section point toward a table.

Two kinds of NULL

SELECT
  order_id,
  shipping_address IS NULL AS no_address,
  shipping_address.city IS NULL AS no_city
FROM orders

A field read through a NULL struct is NULL, so order 1003 reports both. A pickup order could hold a struct with country set and city NULL, and would report no_address = false with no_city = true: a partial address, a different state from no address. The struct also separates no address from an address whose every field is NULL, where 4 flat columns, all NULL, could mean either one.

Structs inside structs

Fields can themselves be structs. Adding geo ROW(lat DOUBLE, lon DOUBLE) inside shipping_address gives shipping_address.geo.lat, and the same NULL rule applies at every level. Building a value by hand uses ROW(...) with a cast to name the fields:

SELECT
  CAST(
    ROW('Torstrasse 1', 'Berlin', '10119', 'DE')
    AS ROW(street VARCHAR, city VARCHAR, postal_code VARCHAR, country VARCHAR)
  ) AS address

An orders table stores a customer struct with customer_id, name and email copied in at purchase time. The customer later changes their email. What do last year's orders show?

• The old email, because each order holds its own copy of the struct and nothing links it back to the customer table
• The new email, because the struct references the customer row and reads the current value on every query
• The new email on orders from the last 90 days and the old email on anything older than that
• NULL, because the engine detects that the copied email no longer matches the customer table and clears it

About this lesson

This section is part of the Nested Data lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.