【BigQuery】配列の展開でハマった話

こんにちは。データアナリティクス事業部です。

今回は、配列構造のテーブルからデータ抽出した際に少し詰まったことがあるので、その話についてご紹介させていただきます。

UNNESTと空配列について

以下のようなテーブルがあるとします。

table_A

table_B

どちらも同じテーブルのように見えますが、table_Aとtable_Bのitemsを以下のようにUNNESTすると、、、

SELECT
  id, item
FROM table_A -- またはtable_B
CROSS JOIN UNNEST(items) AS item;

table_Aの抽出結果

table_Bの抽出結果

抽出結果が変わってしまいました。

実は、table_Aのid = 1のitemsは空配列になっているのに対し、table_Bは空文字配列になっているのです。

空文字配列 ([""]) はUNNESTされたときに空文字列として出力されますが、空配列 ([]) はUNNESTされたときに無視されます。そのため、table_Aのid = 1のレコードが消えてしまったのです。

NULLの場合の補足

NULLも空配列と同様に、CROSS JOINするとレコードが消えてしまいます。  

空配列またはNULLのときに、レコードが消えないようにUNNESTするには、COALESCE関数を使ってNULLや空配列を特定の値に置き換える方法などがあります。

以下のクエリでは、COALESCEを使用して、NULLや空配列を[NULL]に置き換えています。

WITH t AS (
  SELECT 1 AS id, [null] AS arr union all
  SELECT 2, null union all
  SELECT 3, [] union all
  SELECT 4, [1, 2] 
)
SELECT
  id, value
FROM t
CROSS JOIN 
  UNNEST(IF(arr IS NULL OR ARRAY_LENGTH(arr) = 0, [NULL], arr)) value

抽出結果

おわりに

私が携わっている業務で、古いマスタテーブルから新しいマスタテーブルを参照するようFROM句のテーブル名を差し替えたところ、集計ロジックはかわっていないのにデータが消えてしまうという現象が起きました。 調査したところ、配列の要素が変わってしまったことが原因だとわかり、今回の記事を作成しました。

配列構造のデータを扱う際は、配列の要素がどうなっているかに注意する必要があります。

データ分析に興味のある方募集中！

コグラフ株式会社データアナリティクス事業部ではPythonやSQLの研修を行った後、実務に着手します。興味がある方は、下記リンクよりお問い合わせください！

X(Twitter)もやってます！

コグラフではX(Twitter)でも情報を発信しています。

データ分析に興味がある、データアナリストになりたい人など、ぜひフォローお願いします！

📢Wantedly新掲載！

「データに興味がある」
「データに携わる仕事がしたい」
そこのあなた！
私たちと一緒にデータ分析しませんか？
IT業界未経験の方も大歓迎です☺️#エンジニア転職 #データ分析 #駆け出しエンジニアと繋がりたいhttps://t.co/S9o7VSjGRt

— アラリコ@コグラフ株式会社 | データ事業部 (@CographData) July 14, 2023