Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 37 ยท Nested and Repeated Data

Arrays and UNNEST in BigQuery

Learn BigQuery arrays: creating ARRAY values, indexing with OFFSET and ORDINAL, ARRAY_LENGTH, UNNEST with CROSS JOIN, WITH OFFSET, ARRAY_AGG, ARRAY_CONCAT and the NULL rules, with JSON stand-ins.

Most databases force you to model a list, such as the tags of a product or the items of an order, as a separate table. BigQuery can keep the list inside the row as an ARRAY. This is one of its signature features: it makes some queries simpler, avoids joins and often reduces cost. It also confuses newcomers, because a single row can now hold many values.

BigQuery arrays do not exist in SQLite. For runnable practice we use SQLite's JSON arrays with json_each, which plays the role of UNNEST. Each stand-in is labelled, and the real BigQuery syntax is shown read-only beside it.

Creating arrays

An array is an ordered list of values of the same type:

SQL
SELECT
  [1, 2, 3]                  AS numbers,
  ['red', 'green', 'blue']   AS colors,
  ARRAY<STRING>[]            AS empty_array,
  GENERATE_ARRAY(10, 50, 10) AS tens,        -- [10, 20, 30, 40, 50]
  SPLIT('a-b-c', '-')        AS parts;       -- ['a', 'b', 'c']

In a table schema this is a column of mode REPEATED. There are two important rules:

  1. An array cannot contain another array directly (arrays of arrays are not allowed; wrap the inner array in a STRUCT).
  2. An array cannot contain NULL when stored in a table (query results may, but a write fails). A NULL array is read back as an empty array.

Indexing

SQL
SELECT
  colors[OFFSET(0)]   AS first_zero_based,   -- 'red'
  colors[ORDINAL(1)]  AS first_one_based,    -- 'red'
  colors[SAFE_OFFSET(9)] AS out_of_range_is_null,
  ARRAY_LENGTH(colors) AS n                  -- 3
FROM (SELECT ['red', 'green', 'blue'] AS colors);

OFFSET is zero-based and ORDINAL is one-based; a plain out-of-range index raises an error, so use the SAFE_ forms (SAFE_OFFSET, SAFE_ORDINAL) when the length is unknown.

UNNEST: arrays to rows

UNNEST turns an array into a table with one row per element. Joining it to its own row is how you "explode" the list:

SQL
SELECT p.name, tag
FROM `my-project.shop.products` AS p
CROSS JOIN UNNEST(p.tags) AS tag;

Shorthand: a comma means cross join, so FROM products AS p, UNNEST(p.tags) AS tag is identical. Each product repeats once per tag, and a product with no tags disappears (an empty array gives zero rows in a cross join). Keep such rows with a left join:

SQL
SELECT p.name, tag
FROM `my-project.shop.products` AS p
LEFT JOIN UNNEST(p.tags) AS tag;

To get the element position, add WITH OFFSET:

SQL
SELECT tag, pos
FROM UNNEST(['red', 'green', 'blue']) AS tag WITH OFFSET AS pos
ORDER BY pos;

Here is the idea in a runnable form. We store a JSON array of tags and use json_each to explode it, which is the SQLite stand-in for UNNEST:

SQL Stand-in: explode a list of tags (json_each plays UNNEST) ยท database: shop

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

Notice "Mystery Item" disappears, exactly as with CROSS JOIN UNNEST on an empty array. To keep it, use a left join:

SQL Stand-in: keep rows with empty lists (LEFT JOIN) ยท database: shop

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

Counting and filtering with arrays

SQL
-- how many tags?
SELECT name, ARRAY_LENGTH(tags) AS tag_count FROM `my-project.shop.products`;

-- rows whose array contains a value
SELECT name FROM `my-project.shop.products` WHERE 'wood' IN UNNEST(tags);

-- rows where some element matches a condition
SELECT name FROM `my-project.shop.products`
WHERE EXISTS (SELECT 1 FROM UNNEST(tags) AS t WHERE t LIKE 'eco%');

x IN UNNEST(array) is the clean way to test membership. It does not multiply rows, which is the main advantage over a full UNNEST join when you only want to filter.

SQL Stand-in: membership test and counting elements ยท database: shop

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

Building arrays with ARRAY_AGG and ARRAY()

The reverse direction, rows to array, uses ARRAY_AGG (grouped) or an ARRAY(subquery) expression:

SQL
SELECT
  customer_id,
  ARRAY_AGG(id ORDER BY ordered_at) AS order_ids
FROM `my-project.shop.orders`
GROUP BY customer_id;

SELECT
  name,
  ARRAY(SELECT x * 2 FROM UNNEST([1, 2, 3]) AS x) AS doubled
FROM UNNEST(['demo']) AS name;

ARRAY_AGG skips nothing by default and errors on NULL elements in the final result unless you add IGNORE NULLS. Other array helpers: ARRAY_CONCAT(a, b), ARRAY_REVERSE, ARRAY_TO_STRING(arr, ', '), GENERATE_ARRAY, GENERATE_DATE_ARRAY.

The SQLite counterpart of ARRAY_AGG is json_group_array:

SQL Stand-in: rows into a list (json_group_array plays ARRAY_AGG) ยท database: shop

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

Why arrays reduce cost and joins

Storing order items inside the order row means "orders with their items" needs no join, and the data is read from one table. Arrays make sense when the elements belong to one parent and are usually read together. They are a poor fit when elements are shared across parents, or are updated individually and often.

flowchart LR
  subgraph Normalised
    O1["orders"] --- I1["order_items"]
  end
  subgraph Nested
    O2["orders with items ARRAY"]
  end

The nested form keeps parent and children in one table row, so queries read them together.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does CROSS JOIN UNNEST(tags) do to a row whose tags array is empty?

    An empty array yields no rows in a cross join. Use LEFT JOIN UNNEST to keep the parent row.
  2. 2. What is the difference between OFFSET and ORDINAL?

    arr[OFFSET(0)] and arr[ORDINAL(1)] return the first element.
  3. 3. Which is the cleanest way to test whether an array contains a value?

    IN UNNEST tests membership without multiplying rows.
  4. 4. Which function turns grouped rows into an array?

    ARRAY_AGG collects the values of a group into one array.

Summary

  • An ARRAY holds an ordered list of same-type values inside one row (a repeated field).
  • Index with OFFSET/ORDINAL and their SAFE_ forms; get the size with ARRAY_LENGTH.
  • UNNEST flattens arrays to rows; cross join drops empty arrays, left join keeps parents.
  • ARRAY_AGG and ARRAY(subquery) build arrays; IN UNNEST tests membership.
  • Nesting avoids joins when children belong to one parent.

Next: STRUCT: Records Inside Rows