STRUCT in BigQuery

A STRUCT groups named fields into one value, like a row inside a column. How to build and read structs, why = ignores field names, why ORDER BY and < don't work on them, and how reading one field costs less.

A STRUCT is a value made of named fields, each with its own type. It works like a small row inside one column: an address with city and zip, a device with os and model. Combined with arrays, structs let one BigQuery row hold what other databases spread across several joined tables.

Building a struct

There are three ways to write one. STRUCT(... AS name) takes field names from the aliases, STRUCT<...>(...) declares the types, and a plain tuple (a, b) builds a struct with anonymous fields:

SELECT
  STRUCT('Ada' AS name, 36 AS age) AS named,
  STRUCT<city STRING, zip STRING>('Paris', '75001') AS typed,
  (1, 'one') AS tuple_form

Anonymous fields can’t be referenced by name. In results BigQuery names them _field_1, _field_2 and so on, so give fields names whenever the struct leaves the query.

Reading fields

Use dots, as deep as the nesting goes:

WITH customers AS (
  SELECT 'c1' AS id, STRUCT('Paris' AS city, STRUCT('FR' AS code, 'France' AS name) AS country) AS address
)
SELECT
  id,
  address.city,
  address.country.name AS country_name
FROM customers

In Querylab.io, typing address. in a query offers the struct’s fields as completions, one level at a time (Hierarchical completions).

struct.* expands a struct into one column per field:

WITH customers AS (
  SELECT 'c1' AS id, STRUCT('Paris' AS city, '75001' AS zip) AS address
)
SELECT id, address.*
FROM customers

Comparing structs

= compares structs field by field, by position. Field names are ignored, so two structs with different names but the same values in the same order are equal, while the same fields in a different order are not:

SELECT
  STRUCT(1 AS a, 2 AS b) = STRUCT(1 AS x, 2 AS y) AS names_ignored,
  STRUCT(1 AS a, 2 AS b) = STRUCT(2 AS b, 1 AS a) AS order_matters

Ordering comparisons aren’t defined for structs at all:

SELECT STRUCT(1 AS a, 2 AS b) < STRUCT(1 AS a, 3 AS b) AS smaller

The same goes for ORDER BY. Sort by the fields instead, ORDER BY s.a, s.b:

WITH t AS (
  SELECT STRUCT(2 AS a) AS s UNION ALL
  SELECT STRUCT(1)
)
SELECT s
FROM t
ORDER BY s

GROUP BY, DISTINCT and joins on a struct work, using the same field-by-field equality.

Matching several columns at once

A tuple is a struct, so (a, b) IN ((...), (...)) matches pairs of values. This is shorter than chaining ORs and reads like the composite key it is:

WITH stock AS (
  SELECT 'paris' AS store, 'apple' AS sku, 12 AS qty UNION ALL
  SELECT 'paris', 'pear', 4 UNION ALL
  SELECT 'lyon', 'apple', 7 UNION ALL
  SELECT 'lyon', 'pear', 0
)
SELECT store, sku, qty
FROM stock
WHERE (store, sku) IN (('paris', 'pear'), ('lyon', 'apple'))
ORDER BY store

A row is a struct

The alias of a table in FROM refers to the whole row as a struct. Use it to turn rows into JSON, hashing a whole row with FARM_FINGERPRINT(TO_JSON_STRING(t)), or passing a row to a function:

WITH orders AS (
  SELECT 'o1' AS order_id, 30 AS amount UNION ALL
  SELECT 'o2', 45
)
SELECT o.order_id, TO_JSON_STRING(o) AS row_json
FROM orders AS o
ORDER BY o.order_id

Collecting rows into an array of structs

ARRAY_AGG(STRUCT(...)) packs the child rows of each group into one column. This is how you build nested data from flat tables, the reverse of UNNEST:

WITH lines AS (
  SELECT 'o1' AS order_id, 'apple' AS sku, 3 AS qty UNION ALL
  SELECT 'o1', 'pear', 1 UNION ALL
  SELECT 'o2', 'plum', 6
)
SELECT
  order_id,
  ARRAY_AGG(STRUCT(sku, qty) ORDER BY sku) AS order_lines
FROM lines
GROUP BY order_id
ORDER BY order_id

Inside a subquery, SELECT AS STRUCT a, b does the same for a single row, as in ARRAY(SELECT AS STRUCT sku, qty FROM ...).

A NULL struct vs NULL fields

A struct column can be NULL as a whole, or be present with NULL fields. Reading a field of a NULL struct returns NULL rather than an error, so both rows below have a NULL city, but only the first has a NULL address:

WITH t AS (
  SELECT CAST(NULL AS STRUCT<city STRING>) AS address UNION ALL
  SELECT STRUCT(CAST(NULL AS STRING))
)
SELECT
  address IS NULL AS struct_is_null,
  address.city IS NULL AS city_is_null
FROM t

Each field is its own column on disk

BigQuery stores every leaf field of a struct as a separate column. Reading one field reads only that field. In bigquery-public-data.samples.github_nested, the repository struct has 20 fields. Selecting the struct reads all of them, 484.0 MB:

SELECT repository
FROM `bigquery-public-data.samples.github_nested`

Selecting one field reads 12.8 MB, under 3% of that:

SELECT repository.language
FROM `bigquery-public-data.samples.github_nested`

So nesting costs nothing at query time as long as you name the fields you need. SELECT struct_column or SELECT * is what gets expensive. In Querylab.io, the byte estimate in the status bar shows the difference before you run either query. See Nested and repeated data and How a query is billed.

Changing a struct column

You can add a field to an existing struct column, but not with SQL: ALTER TABLE ADD COLUMN only adds top-level columns. Add nested fields by updating the table schema with bq update or the API. New fields always go at the end of the struct. See Schema changes.