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