BigQueryで横持ちを縦持ちに変換

こう言う横持ちになってるテーブルを

こう言う縦持ちのテーブルに変えたいと思ったら

こんな記事を見つけたのでやってみた

How to unpivot in BigQuery?

実装してみた

上記に書いたようにフィールド名に「d_xx」のような規則性があれば
UDFのunpivotを呼び出して縦持ちに変換できた
`sample-project.sample.pivot`を自身のプロジェクトのテーブルに変えて
'd_'を自身の規則性に準じたものに変えれば動くはず

CREATE TEMP FUNCTION 
 unpivot(x ANY TYPE, col_regex STRING) AS (
 (
   SELECT
     ARRAY_AGG(
       STRUCT(
         REGEXP_EXTRACT(y, '[^"]*') AS key,
         REGEXP_EXTRACT(y, r':([^"]*)\"?[,}\]]') AS value 
       )
     )
   FROM
     UNNEST(
       (
         SELECT
           REGEXP_EXTRACT_ALL(json,col_regex||r'[^:]+:\"?[^"]+\"?') arr
         FROM (
           SELECT
             TO_JSON_STRING(x) json
         )
       )
     ) y 
 )
);
SELECT
 id,
 unpivotted
FROM
 `sample-project.sample.pivot` x,
 UNNEST(unpivot(x, 'd_')) unpivotted

各レコードをjson文字列に変換して
正規表現で「d_」を引っこ抜き、keyとvalueをそれぞれカラムに突っ込んでいく感じ

コピペしただけだけど
あとで思い出すようメモ