BigQueryで配列に添字をつける(複数の配列をzipする)

BigQueryのStandard SQLを使っていて配列から位置を指定して要素を取り出すことはできるのですが、UNNEST()を使ってそれぞれの要素を取り出した時にある要素が何番目かという情報を一緒に得る方法がわからなかったのでやり方を考えました(もしかしたら簡単に得る方法があるかも？)

余談ですが、最近BigQueryに触っていて意外とSQLでなんでも書けるなぁと思い始めてきました(BigQueryはWITHの再帰ができないのが残念ですが)

追記
添字を得るだけなら簡単に書けました
sucrose.hatenablog.com

CROSS JOINによる方法

配列の長さがわからないのでGENERATE_ARRAY(1, 100)のように1から十分な長さまでの配列を作って、それとCROSS JOINしてi番目の要素を取り出す関数のSAFE_ORDINAL()(0-indexedならSAFE_OFFSET())で配列から要素を取り出します

#StandardSQL
WITH data AS (SELECT SPLIT('a,b,c', ',') AS split)

SELECT
  i
  , split[SAFE_ORDINAL(i)] AS token
FROM data, UNNEST(GENERATE_ARRAY(1, 100)) AS i
WHERE NOT split[SAFE_ORDINAL(i)] IS NULL
ORDER BY i

同様に2つの配列の値を引いてくればzip的に使うこともできます

#StandardSQL
WITH 
  data1 AS (SELECT SPLIT('a,b,c', ',') AS split)
  , data2 AS (SELECT SPLIT('あ,い,う,え,お', ',') AS split)

SELECT
  i
  , data1.split[SAFE_ORDINAL(i)] AS token
  , data2.split[SAFE_ORDINAL(i)] AS token2
FROM data1, data2, UNNEST(GENERATE_ARRAY(1, 100)) AS i
WHERE NOT data1.split[SAFE_ORDINAL(i)] IS NULL
AND NOT data2.split[SAFE_ORDINAL(i)] IS NULL
ORDER BY i

UDF(ユーザー定義関数)による方法

BigQueryのSQLではJavaScriptによるUDFが動かせます

• Standard SQL User-Defined Functions  |  BigQuery  |  Google Cloud

以下のコード例のようにJavaScriptの関数を定義するだけで簡単に使い回しができます

#StandardSQL
CREATE TEMPORARY FUNCTION array_with_index(x ARRAY<STRING>)
RETURNS ARRAY<STRUCT<index INT64, value STRING>>
LANGUAGE js AS """
  return x.map(
    (value, index) => ({value, 'index': index + 1})
  );
""";

WITH data AS (SELECT SPLIT('a,b,c', ',') AS split)

SELECT
  array_with_index(split) AS result
FROM data

配列をzipする場合も同様にUDFを定義して使えばよいです(型名とカラム名を指定しないといけないのであまり汎用的にはならないですが)

#StandardSQL
CREATE TEMPORARY FUNCTION array_with_index(x ARRAY<STRING>, y ARRAY<STRING>)
RETURNS ARRAY<STRUCT<index INT64, value1 STRING, value2 STRING>>
LANGUAGE js AS """
  return x.map(
    (value, index) => ({'value1': value, 'value2': y[index], 'index': index + 1})
  );
""";

WITH 
  data1 AS (SELECT SPLIT('a,b,c', ',') AS split)
  , data2 AS (SELECT SPLIT('あ,い,う,え,お', ',') AS split)

SELECT
  array_with_index(data1.split, data2.split) AS result
FROM data1, data2