# Values referenced in UNNEST must be arrays > UNNEST takes an array and turns it into rows. The message names the type you gave it. For text, split it first. For JSON, use JSON_QUERY_ARRAY. For a number, build an array with GENERATE_ARRAY. Source: https://docs.querylab.io/bigquery-errors/values-referenced-in-unnest-must-be-arrays/ Updated: 2026-11-04 ```text Values referenced in UNNEST must be arrays. UNNEST contains expression of type STRING at [3:20] ``` ## What BigQuery means `UNNEST` turns an array into a table with one row per element. Whatever you put inside the brackets must be an array. The message says what it got instead: here a `STRING`, with the position of that expression. The type in the message is the useful part. The same error with `INT64`, `JSON` or `STRUCT<...>` points to a different fix, listed below. It is raised when the query compiles, so a dry run reports it and nothing is read or billed. ## The smallest query that reproduces it ```sql WITH posts AS (SELECT 1 AS post_id, 'bq,sql,docs' AS tags) SELECT post_id, tag FROM posts, UNNEST(tags) AS tag ``` ```text Values referenced in UNNEST must be arrays. UNNEST contains expression of type STRING at [3:20] ``` The column `tags` holds one string with commas in it. It looks like a list to a person, and it is text to BigQuery. ## The fixes, by the type in the message **`STRING` that holds a list: split it.** ```sql WITH posts AS (SELECT 1 AS post_id, 'bq,sql,docs' AS tags) SELECT post_id, tag FROM posts, UNNEST(SPLIT(tags, ',')) AS tag ``` This returns three rows: `bq`, `sql` and `docs`. **`STRING` or `JSON` that holds a JSON array: parse it.** Neither a string nor a `JSON` value can be unnested directly: ```sql WITH events AS (SELECT 1 AS event_id, '["click","view"]' AS actions) SELECT event_id, action FROM events, UNNEST(actions) AS action ``` ```text Values referenced in UNNEST must be arrays. UNNEST contains expression of type STRING at [3:21] ``` With a `JSON` column the message ends `expression of type JSON at [3:21]`. `JSON_QUERY_ARRAY` accepts both. On a `STRING` it returns an array of `STRING` values that hold JSON text, and on a `JSON` column it returns an array of `JSON` values: ```sql WITH events AS (SELECT 1 AS event_id, '["click","view"]' AS actions) SELECT event_id, JSON_VALUE(action) AS action FROM events, UNNEST(JSON_QUERY_ARRAY(actions)) AS action ``` This returns `click` and `view`. The elements keep their JSON quotes, as in `"click"`, until `JSON_VALUE` reads them. See [JSON](https://docs.querylab.io/bigquery/json/). **`INT64` or another number: build an array.** If you wanted a series of numbers, make one: ```sql WITH sizes AS (SELECT 3 AS n) SELECT x FROM sizes, UNNEST(GENERATE_ARRAY(1, n)) AS x ``` This returns `1`, `2` and `3`. `UNNEST(n)` on the same table fails with `expression of type INT64 at [3:20]`. **`STRUCT<...>`: unnest the array inside the struct.** A struct is one value, even when one of its fields is an array: ```sql WITH rows_ AS (SELECT STRUCT([1, 2] AS vals) AS s) SELECT v FROM rows_, UNNEST(s.vals) AS v ``` `UNNEST(s)` fails with `expression of type STRUCT> at [3:20]`. The query above returns `1` and `2`. See [Structs](https://docs.querylab.io/bigquery/structs/). **`NULL` written as a bare word.** `SELECT x FROM UNNEST(NULL) AS x` fails with `expression of type INT64`, because a bare `NULL` is not an array. Give it a type: `UNNEST(CAST(NULL AS ARRAY))` runs and returns no rows. ## The variants of the message Each message was produced by BigQuery. **`IN UNNEST`** has its own wording and the same cause: ```sql WITH t AS (SELECT 1 AS id, 'a' AS tags) SELECT id FROM t WHERE 'a' IN UNNEST(tags) ``` ```text Second argument of IN UNNEST must be an array but was STRING at [2:28] ``` `'a' IN UNNEST(SPLIT(tags, ','))` is the fix for a comma-separated `tags`. Other types give the same message with the type changed, for example `BOOL`, `DATE`, `FLOAT64` and `BYTES`. A subquery that returns one value gives `INT64` too: `UNNEST((SELECT 1))`. ## Queries that look wrong but run **An empty or `NULL` array removes the row.** With a comma join, a row whose array is `NULL` or empty (`[]`) has nothing to join to, so it disappears from the result: ```sql WITH t AS (SELECT 1 AS id, CAST(NULL AS ARRAY) AS a UNION ALL SELECT 2, [5]) SELECT id, x FROM t, UNNEST(a) AS x ``` This returns only `id = 2`. To keep row 1 with a `NULL` element, use `LEFT JOIN UNNEST(a) AS x`, which returns both rows. **A scalar subquery that returns an array.** `SELECT * FROM UNNEST((SELECT [1, 2]))` runs and returns two rows. So does `UNNEST(ARRAY(SELECT 1))`. ## Different errors that look like this one | Message | Cause | | --------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `Cannot access field a on a value with type ARRAY> at [1:55]` | You read a field of an array without unnesting it. See [Cannot access field on a value with type ARRAY](https://docs.querylab.io/bigquery-errors/cannot-access-field-on-a-value-with-type-array/). | | `Cannot construct array with element type ARRAY because nested arrays are not supported at [1:23]` | `UNNEST([[1, 2], [3]])`. An array cannot hold arrays. Put each inner array in a struct field: `UNNEST([STRUCT([1, 2] AS v), STRUCT([3] AS v)])` works. | ## In Querylab.io The editor does not check what `UNNEST` receives, so nothing is marked as you type. BigQuery's dry run reports the error, and Querylab.io shows it when you pause typing. The marker is one character wide, on the first character of the expression inside `UNNEST(`, the position in the message. A failed run puts the message in the status bar. There is no one-click fix for this error. Add `SPLIT`, `JSON_QUERY_ARRAY` or `GENERATE_ARRAY` by hand. For how arrays and `UNNEST` work, see [UNNEST](https://docs.querylab.io/bigquery/unnest/) and [Arrays](https://docs.querylab.io/bigquery/arrays/). ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Query syntax, UNNEST operator](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest_operator) - [JSON functions, JSON_QUERY_ARRAY](https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_query_array)