Convert Redshift Super Array of String values to a comma separated string

Question

I have values in Redshift that are super data types and look like ["a", "b", "c"].

I'm trying to write a function like array_join in Athena SQL where it combines all the pieces with a separator. So for the example above, it would return a varchar value of a, b, c.

I have permission to create python/sql user defined functions if needed but python UDFs can't read in super data types.
I've tried converting the array to a subquery table and use partiql to unnest but redshift doesn't let me unnest the subquery on leader or something. My approach there is to then listagg the values back together. I was able to make a table instead of the subquery and unnest/listagg the values successfully but sql functions don't allow multi-commands.

I've also tried splitting the sql array into rows by using guides online involving a seq_0_5 view and adjusting the code to super functions. This didn't work because redshift disconnects me when I try to join tables using an inequality.

Let me know if I'm getting close but it feels like I've hit 2 dead-ends.

Answer 1

In case anyone runs into this problem, I found the solution! There is a json function called json_serialize that converts the super to a string. Then you just need to trim off the brackets and maybe replace out the double quotes.