# 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. Source: https://docs.querylab.io/bigquery-errors/column-of-type-array-cannot-be-used-in-select-distinct/ Updated: 2026-11-10 ```text 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 ```sql WITH t AS (SELECT [JSON '1'] AS a) SELECT DISTINCT a FROM t ``` ```text Column a of type ARRAY containing JSON cannot be used in SELECT DISTINCT at [1:52] ``` `GROUP BY` gives the other wording: ```sql WITH t AS (SELECT [JSON '1'] AS a) SELECT a, COUNT(*) FROM t GROUP BY a ``` ```text Grouping by expressions of type ARRAY containing JSON is not allowed at [1:71] ``` A plain array of strings or numbers runs in both: ```sql 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: ```sql 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`: ```sql 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` 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: ```sql 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: ```sql 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](https://docs.querylab.io/bigquery/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` column `tags`, mostly for an `ARRAY` 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` | `Partitioning by expressions of type ARRAY containing FLOAT64 is not allowed` | | `ORDER BY tags` | `ORDER BY does not support expressions of type ARRAY` | | `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` | 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 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](https://docs.querylab.io/bigquery/json/) and [Geography](https://docs.querylab.io/bigquery/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` 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` | `tags = ['a','b']` and `JOIN ... ON a.tags = b.tags`. Compare `TO_JSON_STRING(tags)`. | | `IN is not defined for arguments of type ARRAY` | `WHERE tags IN (['a','b'])`. Compare `TO_JSON_STRING(tags)`. | | `Column 'tags' in USING has types that do not support equality comparison: ARRAY` | `JOIN ... USING (tags)`. Join `ON` the text keys. | | `First argument to IN UNNEST of type ARRAY 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` | `MAX` and `MIN` have no order for arrays. Aggregate an element or a length. | | `MIN is not defined for arguments of type ARRAY` | The same, for `MIN`. | | `The argument to ARRAY_AGG must not be an array type but was ARRAY` | 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](https://docs.querylab.io/bigquery-errors/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](https://docs.querylab.io/bigquery/distinct/), [GROUP BY](https://docs.querylab.io/bigquery/group-by/) 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: - [Data types, groupable data types](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#groupable_data_types) - [Query syntax, GROUP BY clause](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#group_by_clause) - [JSON functions, TO_JSON_STRING](https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#to_json_string)