Redshift how to split a stringified array into separate parts

Jwan622 0
Viewed 27

Say I have a varchar column let's say religions that looks like this: ["Christianity", "Buddhism", "Judaism"] (yes it has a bracket in the string) and I want the string (not array) split into multiple rows like "Christianity", "Buddhism", "Judaism" so it can be used in a WHERE clause.

Eventually I want to use the results of the query in a where clause like this:

SELECT ...
FROM religions
WHERE name in 
    (
        <this subquery>
    )

How can one do this?

amazon-redshift amazon-web-services postgresql
2 Answers
MP240

You can do the following.

  1. Create a temporary table with sequence of numbers
  2. Using the sequence and split_part function available in redshift, you can split the values based on the numbers generated in the temporary table by doing a cross join.
  3. To replace the double quote and square brackets, you can use the regexp_replace function in Redshift.
create temp table seq as
with recursive numbers(NUMBER) as
(
select 1 UNION ALL
select NUMBER + 1 from numbers where NUMBER < 28
)
select * from numbers;

select regexp_replace(split_part(val,',',seq.number),'[]["]','') as value
from
(select '["christianity","Buddhism","Judaism"]' as val) -- You can select the actual column from the table here.
cross join
seq
where seq.number <= regexp_count(val,'[,]')+1;
mani bharataraju0
Related