# 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.

Source: https://docs.querylab.io/bigquery/structs/

Updated: 2026-10-14

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:

**named, typed and tuple structs**

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

Result in BigQuery (1 row):

| named | typed | tuple_form |
| --- | --- | --- |
| {name: Ada, age: 36} | {city: Paris, zip: 75001} | {_field_1: 1, _field_2: one} |

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:

**address.country.name**

```sql
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
```

Result in BigQuery (1 row):

| id | city | country_name |
| --- | --- | --- |
| c1 | Paris | France |

In Querylab.io, typing `address.` in a query offers the struct's fields as completions, one level at a time ([Hierarchical completions](https://docs.querylab.io/writing-sql/hierarchical-completions/#structs)).

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

**address.* becomes city and zip**

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

Result in BigQuery (1 row):

| id | city | zip |
| --- | --- | --- |
| c1 | Paris | 75001 |

## 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:

**= ignores names and follows field order**

```sql
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
```

Result in BigQuery (1 row):

| names_ignored | order_matters |
| --- | --- |
| true | false |

Ordering comparisons aren't defined for structs at all:

**< between two structs**

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

BigQuery rejects this query:

```text
Less than is not defined for arguments of type STRUCT<a INT64, b INT64> at [1:8]
```

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

**ORDER BY a struct column**

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

BigQuery rejects this query:

```text
ORDER BY does not support expressions of type STRUCT<a INT64> at [7:10]
```

`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 `OR`s and reads like the composite key it is:

**match store and SKU together**

```sql
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
```

Result in BigQuery (2 rows):

| store | sku | qty |
| --- | --- | --- |
| lyon | apple | 7 |
| paris | pear | 4 |

## 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:

**TO_JSON_STRING(o) for each row**

```sql
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
```

Result in BigQuery (2 rows):

| order_id | row_json |
| --- | --- |
| o1 | {"order_id":"o1","amount":30} |
| o2 | {"order_id":"o2","amount":45} |

## 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](https://docs.querylab.io/bigquery/unnest/):

**order lines nested into each order**

```sql
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
```

Result in BigQuery (2 rows):

| order_id | order_lines |
| --- | --- |
| o1 | [{sku: apple, qty: 3}, {sku: pear, qty: 1}] |
| o2 | [{sku: plum, qty: 6}] |

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:

**a NULL struct and a struct with a NULL field**

```sql
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
```

Result in BigQuery (2 rows):

| struct_is_null | city_is_null |
| --- | --- |
| true | true |
| false | true |

## 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:

**the whole repository struct**

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

BigQuery dry run: this query processes 484.0 MB.

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

**only repository.language**

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

BigQuery dry run: this query processes 12.8 MB.

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. See [Nested and repeated data](https://docs.querylab.io/bigquery/nested-and-repeated-data/) and [How a query is billed](https://docs.querylab.io/bigquery/how-queries-are-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](https://docs.querylab.io/bigquery/schema-changes/).

Build a STRUCT three ways and read the fields back. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Data types, struct type](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#struct_type)

- [Specify nested and repeated columns](https://cloud.google.com/bigquery/docs/nested-repeated)