BigQuery STRUCT: How the Nested Data Type Works

A BigQuery STRUCT is a data type that packs several named fields into a single column, so one column can hold something like STRUCT<city STRING, zip STRING, country STRING> instead of spreading those three values across three columns. It is BigQuery's version of a record nested inside a row, and it is why an address, a set of line-item details, or an event payload can live in one field. You build one with the STRUCT() constructor and read its fields with dot notation: SELECT address.city FROM users. Below is the syntax, how to create and access a struct, how arrays of structs work, how to turn a struct into a string, and the errors people hit first.

What is a STRUCT in BigQuery?

A STRUCT is an ordered collection of named, typed fields grouped under one column. Think of it as a row squeezed inside a single cell: the column address can itself contain city, zip and country. Each field has its own type, and the fields can be different types from each other, which is what separates a struct from an array. An array holds many values of the same type; a struct holds a fixed set of differently typed named fields.

-- a table with a struct column
CREATE TABLE demo.users (
  user_id INT64,
  address STRUCT<city STRING, zip STRING, country STRING>
);

BigQuery uses structs because it is built for nested and repeated data rather than the flat, fully normalized tables of a traditional relational database. Grouping related fields into a struct keeps a record together instead of forcing a join, which is faster to scan and closer to how the data arrives from apps and events.

How to create and access a struct

You create a struct inline with the STRUCT() constructor. Name the fields with AS, or let BigQuery infer names from the expressions.

SELECT
  STRUCT('Austin' AS city, '78701' AS zip, 'US' AS country) AS address

To read a field back out, use dot notation on the column name. This is the everyday operation once your data has structs in it.

SELECT
  user_id,
  address.city,
  address.country
FROM demo.users
WHERE address.country = 'US'

You can nest a struct inside a struct, so event.user.id reaches two levels down. Each dot steps into the next named field. There is no depth limit that matters in practice; the GA4 export nests several levels and queries it exactly this way.

Arrays of structs

The shape you meet most often in real BigQuery data is an array of structs: a column that holds a list of records, like every line item on an order. Its type is written ARRAY<STRUCT<...>>.

-- line_items is ARRAY<STRUCT<sku STRING, qty INT64, price NUMERIC>>
SELECT
  order_id,
  item.sku,
  item.qty * item.price AS line_total
FROM sales.orders,
  UNNEST(line_items) AS item

Because the column is an array, you cannot reach the fields directly; you flatten the array into rows first with UNNEST, then read item.sku off each struct. Trying to write SELECT line_items.sku without unnesting is the single most common struct mistake, and BigQuery rejects it. Once unnested, aggregate across all items with something like SUM(item.qty * item.price) grouped by day.

How is a struct different from a separate table?

A struct keeps related fields inside the parent row instead of pushing them into a child table you would join back. In a normalized Postgres schema you might have an orders table and a separate line_items table linked by order_id. In BigQuery you can store the line items as an ARRAY<STRUCT> right on the order row.

The nested approach is faster to scan because there is no join, and it keeps the data that always travels together in one place. The cost is that you have to learn UNNEST, and that structs are less portable if you later move the data to a database that does not support them. This mirrors the record shape you often get from event streams and from data piped in through an integration layer that connects apps, APIs and databases, where a single payload already carries nested fields.

Convert a struct to a string

To flatten a whole struct into text, for logging, a quick export, or eyeballing the contents, use TO_JSON_STRING. It serializes the struct and its field names into a JSON string.

SELECT
  user_id,
  TO_JSON_STRING(address) AS address_json
FROM demo.users
-- returns {"city":"Austin","zip":"78701","country":"US"}

If you only want one field as a string, read it with dot notation and cast if needed: CAST(address.zip AS STRING). Reach for TO_JSON_STRING when you want the entire struct as a single value; reach for dot notation when you want a specific field.

Common STRUCT errors and gotchas

Three problems account for most struct confusion. First, accessing a field on an array without unnesting: Cannot access field sku on a value with type ARRAY<STRUCT<...>> means the column is an array of structs and you have to UNNEST it before the dot notation works. Second, unnamed fields: if you build a struct as STRUCT('Austin', '78701') without AS names, the fields become anonymous and you can only reach them by position, which is brittle, so always name them.

Third, comparing or grouping by a whole struct: BigQuery lets you GROUP BY a struct and it groups by all fields together, which is occasionally what you want and often a surprise. If you meant to group by one field, name it explicitly. And remember that a struct field can itself be NULL independently of the struct, so address IS NOT NULL and address.zip IS NOT NULL are different checks.

Ask it in plain English instead

Structs and arrays of structs are the part of BigQuery SQL that trips people up most, because the nested shape has to be understood before a normal query will touch it. Agentsql connects read-only to BigQuery, works out where a struct field lives and where an UNNEST belongs from a plain-English question like "average order value by product category last quarter," writes the correct Standard SQL, and shows you the query so an analyst can verify it. See the BigQuery integration for how it connects and the SQL query generator for how the translation works, then see how it works and ask your nested BigQuery data a question in plain English.