Column of type ARRAY containing JSON cannot be used in SELECT DISTINCT
BigQuery now groups plain arrays, so this message means the array holds JSON or GEOGRAPHY. Other clauses refuse even a plain array, each with its own message. The fix is a text key.
Column a of type ARRAY containing JSON cannot be used in SELECT DISTINCT at [1:52]
What BigQuery means
Some types can be compared for sameness and some cannot. SELECT DISTINCT and GROUP BY need to compare, so they refuse a type that cannot be.
BigQuery now groups arrays. This message means the array holds JSON or GEOGRAPHY. Older answers say that BigQuery refuses every array here. It does not any more. An array is groupable when its elements are, and GROUP BY and SELECT DISTINCT are the two places where an array may be compared. Two arrays are in the same group when they have the same length and the same elements in the same order.
This message today means the array holds JSON or GEOGRAPHY, alone or inside a struct. Other clauses refuse even a plain array, each with its own message, listed below.
All of the messages here are raised when the query compiles, so a dry run reports them and nothing is read or billed.
The smallest query that reproduces it
WITH t AS (SELECT [JSON '1'] AS a) SELECT DISTINCT a FROM t
Column a of type ARRAY containing JSON cannot be used in SELECT DISTINCT at [1:52]
GROUP BY gives the other wording:
WITH t AS (SELECT [JSON '1'] AS a) SELECT a, COUNT(*) FROM t GROUP BY a
Grouping by expressions of type ARRAY containing JSON is not allowed at [1:71]
A plain array of strings or numbers runs in both:
WITH t AS (SELECT 1 AS id, ['a','b'] AS tags UNION ALL SELECT 2, ['b','a'] UNION ALL SELECT 3, ['a','b'])
SELECT tags, COUNT(*) AS n FROM t GROUP BY tags
This returns ["a","b"] with 2 and ["b","a"] with 1. The order of the elements counts.
The fix: group by a text key
Turn the value into a string that has the same sameness as the value, and group by that.
TO_JSON_STRING. It works for any array, for JSON and for a struct that holds them:
WITH t AS (SELECT [JSON '1', JSON '2'] AS a UNION ALL SELECT [JSON '1', JSON '2'])
SELECT TO_JSON_STRING(a) AS k, COUNT(*) AS n FROM t GROUP BY k
This returns [1,2] with 2. It keeps ['a,b'] apart from ['a','b'] (["a,b"] and ["a","b"]), keeps ['a', NULL] apart from ['a'], and shows an empty array as [] and a NULL array as null. One exception: for floats, GROUP BY treats 0.0 and -0.0 as the same value, but TO_JSON_STRING writes [0] and [-0], so a computed negative zero ends up in its own group.
ARRAY_TO_STRING. Shorter, for an array of STRING:
WITH t AS (SELECT 1 AS id, ['a','b'] AS tags UNION ALL SELECT 2, ['b','a'] UNION ALL SELECT 3, ['a','b'])
SELECT ARRAY_TO_STRING(tags, ',') AS k, COUNT(*) AS n FROM t GROUP BY k
This returns a,b with 2 and b,a with 1. Two traps: ['a,b'] and ['a','b'] both become a,b, so pick a separator that cannot occur in the data, and NULL elements are skipped, so ['a', NULL] and ['a'] both become a. It needs strings. On an ARRAY<INT64> it fails with No matching signature for function ARRAY_TO_STRING; use TO_JSON_STRING.
A sorted ARRAY_TO_STRING, when the order does not matter. Sort the elements first and ['a','b'] and ['b','a'] fall into one group:
WITH t AS (SELECT 1 AS id, ['a','b'] AS tags UNION ALL SELECT 2, ['b','a'] UNION ALL SELECT 3, ['a','b'])
SELECT ARRAY_TO_STRING(ARRAY(SELECT x FROM UNNEST(tags) AS x ORDER BY x), ',') AS k, COUNT(*) AS n
FROM t GROUP BY k
This returns a,b with 3.
UNNEST, then group again. When you want a row for each element, unnest and group by the element. COUNT(DISTINCT id) counts each row once even if the element repeats in its array:
WITH t AS (SELECT 1 AS id, ['a','b'] AS tags UNION ALL SELECT 2, ['b','a'] UNION ALL SELECT 3, ['a','b'])
SELECT tag, COUNT(DISTINCT id) AS n FROM t, UNNEST(tags) AS tag GROUP BY tag ORDER BY tag
This returns a with 3 and b with 3. See UNNEST.
JSON and GEOGRAPHY columns. TO_JSON_STRING(j) for JSON, and ST_ASTEXT(g) for GEOGRAPHY. For an array of geographies, TO_JSON_STRING(a) gives ["POINT(1 2)"].
The same array in other clauses
A plain array is groupable only in GROUP BY and SELECT DISTINCT. Everywhere else it is refused, and the message names the clause. The messages below were produced by BigQuery for an ARRAY<STRING> column tags, mostly for an ARRAY<STRING> column tags. The position at the end of each message is left out.
| What you wrote | Message |
|---|---|
PARTITION BY tags in a window | Partitioning by expressions of type ARRAY is unsupported |
PARTITION BY an ARRAY<FLOAT64> | Partitioning by expressions of type ARRAY containing FLOAT64 is not allowed |
ORDER BY tags | ORDER BY does not support expressions of type ARRAY<STRING> |
ORDER BY tags inside OVER (...) | Ordering by expressions of type ARRAY is not allowed |
UNION DISTINCT | Type ARRAY is not supported in UNION DISTINCT operation |
INTERSECT DISTINCT or EXCEPT DISTINCT | Type ARRAY is not supported in INTERSECT DISTINCT or EXCEPT DISTINCT operation |
UNION DISTINCT of a struct that holds the array | Type STRUCT is not supported in UNION DISTINCT operation |
COUNT(DISTINCT tags) | Aggregate functions with DISTINCT cannot be used with arguments of type ARRAY |
COUNT(DISTINCT STRUCT(tags)) | Aggregate functions with DISTINCT cannot be used with arguments of type STRUCT |
APPROX_COUNT_DISTINCT(tags) | Unsupported type ARRAY for aggregate function APPROX_COUNT_DISTINCT |
tags IS DISTINCT FROM ['a'] | IS DISTINCT FROM can't be used with arguments of type ARRAY |
CLUSTER BY tags in CREATE TABLE | CLUSTER BY columns may only have simple types, but column tags has type ARRAY<STRING> |
Their fixes:
PARTITION BY:PARTITION BY TO_JSON_STRING(tags). The row numbers then restart for each different array.ORDER BY: order by something about the array:ARRAY_LENGTH(tags),tags[SAFE_OFFSET(0)]for the first element, orTO_JSON_STRING(tags)for a text order.ARRAY_TO_STRING(tags, ',')also works for strings.UNION DISTINCT:UNION ALLthe queries, thenSELECT DISTINCTover the result.SELECT DISTINCT tags FROM (SELECT tags FROM t UNION ALL SELECT tags FROM u)removes the duplicates, and the arrays stay arrays.EXCEPT DISTINCTandINTERSECT DISTINCT: compare keys:WHERE TO_JSON_STRING(tags) NOT IN (SELECT TO_JSON_STRING(tags) FROM u)isEXCEPT, andWHERE NOT EXISTS (SELECT 1 FROM u WHERE TO_JSON_STRING(u.tags) = TO_JSON_STRING(t.tags))does the same. UseINforINTERSECT. Both stand-ins keep duplicate rows. AddDISTINCTto the outerSELECTto match.COUNT(DISTINCT tags):COUNT(DISTINCT TO_JSON_STRING(tags)), orCOUNT(*)overSELECT DISTINCT tags.
JSON and GEOGRAPHY
JSON and GEOGRAPHY are not groupable alone either, and the wording is the same family:
| What you wrote | Message |
|---|---|
SELECT DISTINCT j (JSON) | Column j of type JSON cannot be used in SELECT DISTINCT |
SELECT DISTINCT g (GEOGRAPHY) | Column g of type GEOGRAPHY cannot be used in SELECT DISTINCT |
GROUP BY j | Grouping by expressions of type JSON is not allowed |
GROUP BY g | Grouping by expressions of type GEOGRAPHY is not allowed |
GROUP BY a struct that holds JSON | Grouping by expressions of type STRUCT containing JSON is not allowed |
SELECT DISTINCT a struct that holds JSON | Column a of type STRUCT containing JSON cannot be used in SELECT DISTINCT |
PARTITION BY j | Partitioning by expressions of type JSON is not allowed |
ORDER BY j | ORDER BY does not support expressions of type JSON |
UNION DISTINCT on j | Column 1 in UNION DISTINCT has type that does not support set operation comparisons: JSON |
INTERSECT DISTINCT or EXCEPT DISTINCT | the same, with INTERSECT DISTINCT or EXCEPT DISTINCT in place of UNION DISTINCT |
COUNT(DISTINCT j) | Aggregate functions with DISTINCT cannot be used with arguments of type JSON |
a.j = b.j in a join | Equality is not defined for arguments of type JSON |
|> DISTINCT with an array of JSON | Column a of type ARRAY containing JSON is not supported in pipe DISTINCT |
|> DISTINCT with j | Column j of type JSON is not supported in pipe DISTINCT |
CLUSTER BY j in CREATE TABLE | CLUSTER BY expression must be groupable, but type is JSON |
Use TO_JSON_STRING(j) for JSON, and ST_ASTEXT(g) for GEOGRAPHY. To group by one value inside the JSON, read it out: SELECT DISTINCT STRING(j.a) runs. See JSON and Geography.
Other types the same family refuses
A few more messages have the same shape. Their cause is not an array.
A FLOAT64 in PARTITION BY. Partitioning by expressions of type FLOAT64 is not allowed. Floats can be grouped but not used to partition a window. Cast to NUMERIC or STRING first: PARTITION BY CAST(f AS NUMERIC) runs.
A struct in ORDER BY or PARTITION BY. ORDER BY does not support expressions of type STRUCT<x INT64> and Partitioning by expressions of type STRUCT is unsupported. Use a field of the struct: ORDER BY s.x, s.y and PARTITION BY s.x run.
Queries that look wrong but run
GROUP BY ALL, ROLLUP, CUBE and GROUPING SETS on an array. They accept it, like GROUP BY does.
A struct that holds an array (not JSON): SELECT DISTINCT STRUCT(id, tags) AS s FROM t runs, and so does GROUP BY on a struct column that holds one.
UNION ALL of arrays. Only DISTINCT needs the comparison. SELECT tags FROM t UNION ALL SELECT tags FROM t runs.
ANY_VALUE(tags) returns an array per group without comparing it.
Different errors that look like this one
| Message | Cause |
|---|---|
Equality is not defined for arguments of type ARRAY<STRING> | tags = ['a','b'] and JOIN ... ON a.tags = b.tags. Compare TO_JSON_STRING(tags). |
IN is not defined for arguments of type ARRAY<STRING> | WHERE tags IN (['a','b']). Compare TO_JSON_STRING(tags). |
Column 'tags' in USING has types that do not support equality comparison: ARRAY<STRING> | JOIN ... USING (tags). Join ON the text keys. |
First argument to IN UNNEST of type ARRAY<STRING> does not support equality comparison | tags IN UNNEST(...) with an array on the left. 'a' IN UNNEST(tags) is the form that works. |
MAX is not defined for arguments of type ARRAY<STRING> | MAX and MIN have no order for arrays. Aggregate an element or a length. |
MIN is not defined for arguments of type ARRAY<STRING> | The same, for MIN. |
The argument to ARRAY_AGG must not be an array type but was ARRAY<STRING> | You cannot build an array of arrays. Wrap each array in a struct: ARRAY_AGG(STRUCT(tags)). |
HAVING clause expression references column a which is neither grouped nor aggregated | The array was not in the GROUP BY. See Neither grouped nor aggregated. |
Primary key column cannot be of type ARRAY. Invalid column: a. | A PRIMARY KEY on an array column. |
Struct constructors are not supported within group by keys of type STRUCT. | Any STRUCT(...) built in the GROUP BY, or a select-list STRUCT(...) grouped by its alias or ordinal. Build the struct in a subquery and group by the column. |
In Querylab.io
The editor does not check which types can be grouped or compared, so it does not mark these errors as you type. BigQuery’s dry run reports the error when you pause typing. The marker sits at the position in the message. A message without a position, such as Partitioning by expressions of type ARRAY is unsupported, the UNION DISTINCT one and COUNT(DISTINCT tags), is marked at the start of the query. A failed run puts the message in the status bar. There is no one-click fix, because the right key depends on what two arrays should count as the same.
See SELECT DISTINCT, GROUP BY and Arrays.