[SOLVED] How to apply Recursive CTE in Redshift

How to apply Recursive CTE in Redshift

So this is my first time working with Recursive CTE in Redshift. I want to get all value in array using recursive CTE. This is my code so far

WITH
flattened_data AS (
SELECT
    benefit_id,
    rule_value
FROM
    table1
),
array_elements AS (
SELECT
    benefit_id,
    TRIM(BOTH ' []"' FROM SPLIT_PART(rule_value, ',', 1)) AS element,
    1 AS position
FROM
    flattened_data
WHERE
    LENGTH(rule_value) > 2  -- Ensure the array is not empty

UNION ALL

SELECT
    ae.benefit_id,
    TRIM(BOTH ' []"' FROM SPLIT_PART(rule_value, ',', ae.position + 1)) AS element,
    ae.position + 1
FROM
    array_elements ae
JOIN
    flattened_data t1
ON
    ae.benefit_id = t1.benefit_id
WHERE
    TRIM(BOTH ' []"' FROM SPLIT_PART(t1.rule_value, ',', ae.position + 1)) IS NOT NULL
)
SELECT * FROM array_elements

but this is always resulting error ERROR: relation "array_elements" does not exist. Is this how to apply a recursive CTE in Redshift?

Solution

Redshift does this without recursion... and your code (even if you fixed it) would break as soon as a string in your array had a nested comma in it. You can use the below code that leverages unnesting, but better yet declare the rule_value as a SUPER from the start so the copy command imports the data properly instead of as a string.

SELECT benefit_id, element::varchar
from (SELECT benefit_id,
    json_parse(rule_value) arr
  FROM table1) t, t.arr element