Docs/Error messages/ARRAY in SELECT DISTINCT or GROUP BY
Error messagesGrouping

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 wroteMessage
PARTITION BY tags in a windowPartitioning 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 tagsORDER BY does not support expressions of type ARRAY<STRING>
ORDER BY tags inside OVER (...)Ordering by expressions of type ARRAY is not allowed
UNION DISTINCTType ARRAY is not supported in UNION DISTINCT operation
INTERSECT DISTINCT or EXCEPT DISTINCTType ARRAY is not supported in INTERSECT DISTINCT or EXCEPT DISTINCT operation
UNION DISTINCT of a struct that holds the arrayType 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 TABLECLUSTER 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, or TO_JSON_STRING(tags) for a text order. ARRAY_TO_STRING(tags, ',') also works for strings.
  • UNION DISTINCT: UNION ALL the queries, then SELECT DISTINCT over 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 DISTINCT and INTERSECT DISTINCT: compare keys: WHERE TO_JSON_STRING(tags) NOT IN (SELECT TO_JSON_STRING(tags) FROM u) is EXCEPT, and WHERE NOT EXISTS (SELECT 1 FROM u WHERE TO_JSON_STRING(u.tags) = TO_JSON_STRING(t.tags)) does the same. Use IN for INTERSECT. Both stand-ins keep duplicate rows. Add DISTINCT to the outer SELECT to match.
  • COUNT(DISTINCT tags): COUNT(DISTINCT TO_JSON_STRING(tags)), or COUNT(*) over SELECT DISTINCT tags.

JSON and GEOGRAPHY

JSON and GEOGRAPHY are not groupable alone either, and the wording is the same family:

What you wroteMessage
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 jGrouping by expressions of type JSON is not allowed
GROUP BY gGrouping by expressions of type GEOGRAPHY is not allowed
GROUP BY a struct that holds JSONGrouping by expressions of type STRUCT containing JSON is not allowed
SELECT DISTINCT a struct that holds JSONColumn a of type STRUCT containing JSON cannot be used in SELECT DISTINCT
PARTITION BY jPartitioning by expressions of type JSON is not allowed
ORDER BY jORDER BY does not support expressions of type JSON
UNION DISTINCT on jColumn 1 in UNION DISTINCT has type that does not support set operation comparisons: JSON
INTERSECT DISTINCT or EXCEPT DISTINCTthe 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 joinEquality is not defined for arguments of type JSON
|> DISTINCT with an array of JSONColumn a of type ARRAY containing JSON is not supported in pipe DISTINCT
|> DISTINCT with jColumn j of type JSON is not supported in pipe DISTINCT
CLUSTER BY j in CREATE TABLECLUSTER 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

MessageCause
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 comparisontags 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 aggregatedThe 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.