BigQueryのSTRUCT型やARRAY型で無理やりGROUP BYで集計する

BigQueryではSTRUCT(構造体)型やARRAY(配列)型が使えます
データ型  |  BigQuery  |  Google Cloud

STRUCT型のカラムでGROUP BYしようとするとGrouping by expressions of type STRUCT is not allowed atのようなエラーが出てきて実行することができません

同様にARRAY型のカラムだとGrouping by expressions of type ARRAY is not allowed atのようになります

これをどうにかしてGROUP BYすることを考えます

方法1: 構造体の中身を一度展開してGROUP BYする

構造体の場合、展開して新しいSTRUCTに入れ直して、展開したそれぞれのカラムでGROUP BYすると実行できます

SELECT
  STRUCT(s.x, s.y) AS s,
  COUNT(*) AS c
FROM (
  SELECT STRUCT(1 AS x, 1 AS y) AS s
  UNION ALL
  SELECT STRUCT(2 AS x, 2 AS y) AS s
  UNION ALL
  SELECT STRUCT(1 AS x, 1 AS y) AS s
) AS src
GROUP BY src.s.x, src.s.y

もしくはANY_VALUE()を使っても書けます

SELECT
  ANY_VALUE(s) AS s,
  COUNT(*) AS c
FROM (
  SELECT STRUCT(1 AS x, 1 AS y) AS s
  UNION ALL
  SELECT STRUCT(2 AS x, 2 AS y) AS s
  UNION ALL
  SELECT STRUCT(1 AS x, 1 AS y) AS s
) AS src
GROUP BY src.s.x, src.s.y

配列の場合も上の構造体の場合と同様に書けるはずですが、要素数が多いと大変なので次に紹介する方法をおすすめします

方法2: 構造体や配列を文字列に変換してGROUP BYする

構造体をFORMAT()関数で文字列に変換して、この文字列でGROUP BYするという手もあります
'%T'へのフォーマットはBigQueryのリテラルの形の文字列になるので(ほとんどの場合)元の構造体でGROUP BYするのと同じような結果が得られるはずです
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#t_and_t_behavior
というわけで以下のように書くことで、だいたいいい感じにSTRUCT型のカラムで集計できるはずです

SELECT
  ANY_VALUE(s) AS s,
  COUNT(*) AS c
FROM (
  SELECT STRUCT(1 AS x, 1 AS y) AS s
  UNION ALL
  SELECT STRUCT(2 AS x, 2 AS y) AS s
  UNION ALL
  SELECT STRUCT(1 AS x, 1 AS y) AS s
)
GROUP BY FORMAT('%T', s)

この方法はそのままARRAY型のカラムにも適用できます
以下のように書くことで配列をキーとした集計ができます

SELECT
  ANY_VALUE(a) AS a,
  COUNT(*) AS c
FROM (
  SELECT [1, 2] AS a
  UNION ALL
  SELECT [1, 2, 3] AS a
  UNION ALL
  SELECT [1, 2] AS a
)
GROUP BY FORMAT('%T', a)