なにこれ
以下のようなArrayっぽいStringが入っている項目を
user_name(STRING) | values(STRING) |
---|---|
hoge | ["1","2"] |
fuga | ["5"] |
以下のようにばらすクエリの解説です。
user_name(STRING) | value(STRING) |
---|---|
hoge | 1 |
hoge | 2 |
fuga | 5 |
考え方
- ArrayっぽいStringを、Arrayに分割する
1a. SPLITを使う
1b. JavaScriptの関数を使う - 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