redshiftでunnest

redshiftで(N,M,L,....)配列をunnestしてrowに展開しようとしたら、redshiftがunnestサポートしてないみたい😭

擬似的にunnestを再現する方法が微妙に手こずったので備忘録として残しておく。

WITH RECURSIVE seq(index) AS (
    -- 初期値指定
    SELECT 0 AS index
    UNION ALL
    -- 再帰処理部分
    SELECT index + 1 AS cnt FROM seq
    -- 終了値指定
    WHERE index <= JSON_ARRAY_LENGTH('["aaa","bbb","ccc"]')
),
SELECT
    json_extract_array_element_text(tmp.json_ary, seq.index) AS id
FROM (
        select '["aaa","bbb","ccc"]' AS json_ary
    ) AS tmp
INNER JOIN 
    seq 
    ON index < JSON_ARRAY_LENGTH(tmp.json_ary)

再帰を使ってunnestしたいjson_arrayの長さ分のテーブルを連番のテーブル(seq)を作成して、json_extract_array_element_textをつかって、json_arrayのindex番目の要素を順々に取得していく感じ。

「redshift unnest」とかで調べると↑のような方法はけっこう出てくるんだけど再帰処理で、連番のテーブルを再現する方法がわかんなくて詰まってしまった。(RECURSIVE知らなかった🧐 )

参考