Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 37 ยท Nested and Repeated Data

STRUCT: Records Inside Rows in BigQuery

Understand BigQuery STRUCT: create structs, access fields with dot notation, SELECT AS STRUCT, arrays of structs, struct comparison, and how structs relate to nested JSON, with stand-ins.

A STRUCT is a record inside a column: a small bundle of named fields, each with its own type. Where an ARRAY is a list, a STRUCT is like a row nested in a row. A customer's address (street, city, postcode) can be one STRUCT column instead of three separate ones. Combine the two and you get an ARRAY<STRUCT<...>>, the standard way to store things like order items.

BigQuery structs have no SQLite equivalent, so the stand-ins use json_object and json_extract, which model the same dot-access idea. Real BigQuery syntax is read-only and marked.

Creating structs

SQL
SELECT
  STRUCT('Ada' AS first_name, 'Lovelace' AS last_name) AS person,
  STRUCT(1 AS id, ['a', 'b'] AS tags)                 AS thing,
  (1, 'x')                                            AS unnamed_tuple;  -- fields named _field_1, _field_2

The name comes from AS. If you leave names out, BigQuery calls the fields _field_1, _field_2, and so on, which is fine for temporary use. You can also give an explicit type, STRUCT<id INT64, label STRING>(1, 'x').

Dot notation

Reach a field with a dot:

SQL
SELECT
  customer.address.city      AS city,
  customer.address.postcode  AS postcode
FROM `my-project.shop.orders`;

customer is a STRUCT column, address is a STRUCT inside it, and city is a leaf field. You can go as deep as you like (up to 15 levels of nesting). Select a whole struct to get it as one nested value, or customer.* to expand its fields into separate columns:

SQL
SELECT customer.* FROM `my-project.shop.orders`;

Here is the same idea with JSON, which SQLite does support. json_extract takes a path with dots:

SQL Stand-in: dot access into a nested object (JSON) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In BigQuery the same query is customer.name and customer.address.city, with real types instead of text.

Building a struct from a query

SELECT AS STRUCT packs the selected columns into one struct. It is the standard way to create a nested column from a subquery:

SQL
SELECT
  o.id,
  ARRAY(
    SELECT AS STRUCT i.product_id, i.quantity, i.unit_price
    FROM `my-project.shop.order_items` AS i
    WHERE i.order_id = o.id
  ) AS items
FROM `my-project.shop.orders` AS o;

This correlated subquery builds an ARRAY<STRUCT<product_id INT64, quantity INT64, unit_price NUMERIC>>, one array per order, with no join in the output. The SQLite stand-in uses json_group_array and json_object:

SQL Stand-in: build an array of items per order ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Arrays of structs

The most common nested shape in BigQuery is an array of structs. Read it by unnesting, then use dot notation:

SQL
SELECT
  o.id AS order_id,
  item.product_id,
  item.quantity * item.unit_price AS line_total
FROM `my-project.shop.orders_nested` AS o
CROSS JOIN UNNEST(o.items) AS item;

Totals for each order need no second table and no GROUP BY, because the items are in the same row:

SQL
SELECT
  id,
  (SELECT SUM(i.quantity * i.unit_price) FROM UNNEST(items) AS i) AS order_total
FROM `my-project.shop.orders_nested`;

This "subquery over an unnested array" pattern is one of the most useful BigQuery idioms. It aggregates inside the row and never multiplies rows.

SQL Stand-in: unnest the items and total them ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Comparing and using structs

  • Two structs can be compared with = if they have the same field types: STRUCT(1, 'a') = STRUCT(1, 'a') is true.
  • A struct can appear in GROUP BY and DISTINCT, but not in ORDER BY of a column directly (you order by its fields).
  • Struct fields can be NULL, and the struct itself can be NULL.
  • STRUCT columns are stored in the same columnar way: reading customer.address.city scans only that leaf field, not the whole struct. This is why nesting is cheap.

When to use STRUCT

Use a STRUCT when Use separate columns when
Fields belong together (address, geo point, device info) The fields are unrelated
You want to pass a group around or return it from a function Every query filters on them independently
The source is JSON, Avro or Protobuf with nested records Another tool needs a flat table

Structs also give your schema namespaces: device.os and browser.os can both exist without clashing.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How do you read the city field of a struct column named address?

    GoogleSQL uses dot notation for struct fields.
  2. 2. What does SELECT AS STRUCT do?

    It turns each result row into a struct value, typically used inside ARRAY(...).
  3. 3. What does ARRAY<STRUCT<...>> let you model?

    It is the standard nested-and-repeated design.
  4. 4. Reading customer.address.city from a struct column scans:

    Nested leaf fields are stored as their own columns, so BigQuery reads only what you reference.

Summary

  • A STRUCT is a named bundle of typed fields inside one column; access fields with dots.
  • SELECT AS STRUCT and ARRAY(SELECT AS STRUCT ...) build nested values from queries.
  • ARRAY<STRUCT<...>> models parent-child data without a join.
  • A subquery over UNNEST(array) aggregates inside a row without fan-out.
  • Leaf fields of structs are stored as columns, so reads stay cheap.

Next: Nested and Repeated Schemas in Practice