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.
Practice this in free lessons on learn.querylab.io: UNNEST an array of structs and 1 more.
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
WITH posts AS (SELECT 1 AS post_id, 'bq,sql,docs' AS tags)
SELECT post_id, tag
FROM posts, UNNEST(tags) AS tag
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.
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:
WITH events AS (SELECT 1 AS event_id, '["click","view"]' AS actions)
SELECT event_id, action
FROM events, UNNEST(actions) AS action
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:
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.
INT64 or another number: build an array. If you wanted a series of numbers, make one:
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:
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<vals ARRAY<INT64>> at [3:20]. The query above returns 1 and 2. See 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<INT64>)) 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:
WITH t AS (SELECT 1 AS id, 'a' AS tags)
SELECT id FROM t WHERE 'a' IN UNNEST(tags)
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:
WITH t AS (SELECT 1 AS id, CAST(NULL AS ARRAY<INT64>) 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<STRUCT<a INT64>> at [1:55] | You read a field of an array without unnesting it. See Cannot access field on a value with type ARRAY. |
Cannot construct array with element type ARRAY<INT64> 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.