Structs in SQL

A list holds many values of one type. A struct holds a few values of different types, each with a name, like a row packed into a single column. Curly braces build one, with the field names in quotes:

select title,
       {'genre': genre, 'year': release_year, 'rating': rating} as details
from movies
order by movie_id
limit 3

Each film has one details value with three fields: a text genre, a whole-number year, and a fractional rating.

Reading fields

A dot reads one field, the way m.title reads a column from a table alias. Square brackets with the name in quotes do the same thing:

with films as (
  select title,
         {'genre': genre, 'year': release_year, 'rating': rating} as details
  from movies
)
select title,
       details.genre,
       details['year'] as released
from films
where details.rating >= 8.5

Only Paper Lanterns is rated 8.5 or higher. A field the struct doesn't have stops the query: details.director fails with Could not find key "director" in struct, and the message suggests a field the struct does have.

To turn every field back into its own column, use .*:

with films as (
  select title,
         {'genre': genre, 'rating': rating} as details
  from movies
)
select title, details.*
from films
order by title
limit 3

A list of structs

Structs earn their keep inside a list. list(m.title order by r.rental_date) would give a customer's titles but lose the dates. Collect a struct instead, and each item keeps its title and its date together:

select r.customer_id,
       list({'title': m.title, 'rented_on': r.rental_date}
            order by r.rental_date) as rentals
from rentals as r
join movies as m on m.movie_id = r.movie_id
where r.customer_id = 1
group by r.customer_id

That's one row holding four rentals, earliest first. Index the list, then read a field: rentals[1].title is the first film customer 1 rented, and rentals[-1].rented_on is the date of the latest one.

For a single value, such as only the latest title, arg_max(m.title, r.rental_date) is shorter. The list is worth building when you need several things from the same ordered history, as the exercise does.

In other databases

BigQuery has STRUCT with the same dot access, and arrays of structs are how it stores nested data. PostgreSQL has composite types: row(title, rating) builds an unnamed one, and named fields need CREATE TYPE first. Snowflake, MySQL, and SQL Server use a JSON object instead, which is the next lesson.