夕蛙のなく頃に

データアナリストとして学んだことや趣味で勉強し始めたIoTをアウトプットする

BigQueryでArrayっぽいStringをフラット化する

なにこれ

以下のようなArrayっぽいStringが入っている項目を

user_name(STRING) values(STRING)
hoge ["1","2"]
fuga ["5"]

以下のようにばらすクエリの解説です。

user_name(STRING) value(STRING)
hoge 1
hoge 2
fuga 5

考え方

  1. ArrayっぽいStringを、Arrayに分割する
    1a. SPLITを使う
    1b. JavaScriptの関数を使う
  2. UNNESTを使って、Arrayをフラット化する

それぞれ解説していきます。

テストデータ

これ以降の解説でのクエリでは以下を省略します。

WITH test_table AS (
  SELECT 'hoge' AS user_name, '["1","2"]' AS values 
  UNION ALL
  SELECT 'fuga', '["5"]'
)

1. ArrayっぽいStringを、Arrayに分割する

1a. SPLITを使う

SPLIT(values, ',')として分割したいんですが、前後についているカッコが邪魔なので、それを除く処理を入れる必要があります。その後、カンマでSPLITします。

SELECT
  user_name,
  SUBSTR(values, 2, LENGTH(values)-2)
FROM
  test_table
user_name(STRING) f0_(ARRAY)
hoge
"1"
"2"
fuga "5"

1b. JavaScriptの関数を使う

空文字がきたらNULLを、そうでなければJSON.parse()することでArray化して返す関数を定義します。

結果は、ダブルクォーテーションが取れたSTRINGのArrayとして解釈され、1aとはその点が異なります。

CREATE TEMP FUNCTION parse_string_array(s STRING)
  RETURNS ARRAY<STRING>
  LANGUAGE js AS """
if(!s)
  return [null];
else
  return JSON.parse(s);
"""
;

SELECT
  user_name,
  parse_string_array(values)
FROM
  test_table
user_name(STRING) f0_(ARRAY)
hoge
1
2
fuga 5

2. UNNESTを使って、Arrayをフラット化する

1aと1bではダブルクォーテーションがあるかないかの違いがありました。 1aの場合は再度SUBSTRを使うことでダブルクォーテーションを取る必要があります。

1aの場合

WITH array_table AS (
  SELECT
    user_name,
    SPLIT(SUBSTR(values, 2, LENGTH(values)-2), ',') AS values
  FROM
    test_table
)
SELECT
  user_name,
  SUBSTR(v, 2, LENGTH(v)-2) AS value
FROM
  array_table, UNNEST(values) AS v

1bの場合

WITH array_table AS (
  SELECT
    user_name,
    parse_string_array(values) AS values
  FROM
    test_table
)
SELECT
  user_name,
  v AS value
FROM
  array_table, UNNEST(values) AS v