Creating New Rows Based on Column Value

I answered a nearly exact same question a few days ago. How split comma separated string into multiple rows in AWS redshift?

Take a look and see if this gets you unstuck. If not please comment with the issue you have and I'll be happy to address.

===========================================================

As requested I've put together a few ways to attack this:

First following the pattern I used for the previous answer and using no special data types. I did change your single quotes (chr(39)) in your lists to double quotes as this is the JSON standard and this opened up the power of JSON functions.

with recursive numbers(n) as (
  select 0 as n
  union all
  select n + 1 
  from numbers n
  where n < (select max(length(places) - length(replace(places, ',',''))) from mytable)
),
input as (
  select userid, username, display_name, bio, replace(places,chr(39),'"') places, email, 
  length(places) - length(replace(places, ',','')) no_of_elements --counts the number of commas in the string
  from mytable
)
select userid, username, display_name, bio, places, email, json_extract_array_element_text(places, n.n) as place
from input t
join numbers n
on n.n <= t.no_of_elements
order by userid, place;

Next I took your JSON info and converted it to super data type (json_parse). However I kept the same explicit unrolling behavior (numbers CTE) to break things out. This can be easier to understand that the built-in unrolling of supers. Note that since zero elements and 1 element need to be treated the same (join once) I have a decode() in the join on condition.

with recursive numbers(n) as (
  select 0 as n
  union all
  select n + 1 
  from numbers n
  where n < (select max(get_array_length(json_parse(replace(places,chr(39),'"')))) from mytable)
),
input as (
select userid, username, display_name, bio, json_parse(replace(places,chr(39),'"')) as places, email
from mytable
)
select t.*, places[0] as place
from input t
join numbers n
on n.n < decode(get_array_length(t.places),0,1,get_array_length(t.places))
order by userid, place;

Now if you are ready to go all in on supers this can be done with a little trickery and built-in unrolling. Again some action was needed for the case of zero elements in the array - I put an empty string as an element in this case. There are other ways this could be done but I was already modifying the string to make it valid JSON.

with input as (
select userid, username, display_name, bio, json_parse(replace(decode(places,'[]','[""]',places),chr(39),'"')) as places, email
from mytable
) 
select t.*,  place
from input t, t.places as place
order by userid, place;

If you like using supers you may want to change your data column to be super instead of varchar but this will mean changing your data be valid JSON on ingestion.