頭を抱えたOFFSETとORDINALの使い方

目的

OFFSETとORDINALの違いを理解する

背景

BigQueryを使う機会があり、最近クエリを書いています。
ネストされたデータだったこともあり、要望を形にするのに試行錯誤の連続。
学びが多いですが、初心者にとってはかなり頭を抱えた作業でした。
いっちょ前にネストなんて使ってますが、最初は”ネスト”さえなんじゃそりゃと思うほどの初心者でした。（小声）

中でも1番悩まされたのがOFFSETとORDINAL。
以下、公式にもしれっと紹介されているレベルで、個人的には理解が難しかったです。
BigQuery公式ドキュメント
具体例で数字を使っているのでそれもまたなんだかイメージしづらい原因ではと思ってます。。

結論

使用イメージ

ネストされたデータを作成

WITH examples AS
    (SELECT ["bell pepper", "cabbage", "carrot", "onion", "potato", "tomato"] AS vegetables)

SELECT vegetables FROM examples;

実行すると、以下のような1レコードのデータができます。

OFFSETとORDINALを使って出力結果を確認

（先述のSELECT文にOFFSETとORDINALのクエリを追加）

SELECT
    vegetables,
    vegetables[OFFSET(0)] AS OFFSET0,
    vegetables[OFFSET(1)] AS OFFSET1,
    vegetables[ORDINAL(1)] AS ORDINAL1,
    vegetables[ORDINAL(4)] AS ORDINAL4
FROM
    examples;

OFFSET0, ORDINAL1は要素1番目、OFFSET1は要素2番目、ORDINAL4は要素4番目と、
結論で紹介した通りの結果が返ってきました。


なんだ難しくないじゃん！と思ってしまいそうですが、複雑なクエリ内で使うと意外と混乱します。

【おまけ】OFFSETとORDINALで発生するERROR

今回の例の場合、指定可能なインデックスはOFFSETが0～5、ORDINALが1～6です。
よってインデックスの範囲外を指定すると
「Array index X is out of bounds (overflow)」と怒られます。（※Xは数が入る）
以下NG例です。

怒られたら、OFFSETとORDINALの指定が間違っているので、見直しましょう。

関連記事も執筆予定です✐

• Tips
• 技術情報