split a field in redshift

Question

I have a my table in redshift that contains some concatenated id

Product_id , options_id
1,           2
5,           5;9;7
52,          4;5;8,11

I want to split every my table like this:

 Product_id , options_id
 1 ,           2
 5,            5
 5,            9
 5,            7
 52,           4
 52,           5
 52,           9

in the documentation of redshift, i find a similar function 'split part' but with this function i must enter the number of the part that i want to get exp:

Product_id , options_id
5,          5;9;7

split_part(options_id,';',2) will return 9,

Any help please Thanks.

Answer 1

So, the problem here is to take one row and split it into multiple rows. That's not too hard in PostgreSQL -- you could use the unnest() function.

However, Amazon Redshift does not implement every function available in PostgreSQL, and unnest() is unsupported.

While it is possible to write a User Defined Function in Redshift, the function can only return one value, not several rows.

A good option is to iterate through each part, extracting each in turn as a row. See the workaround in Error while using regexp_split_to_table (Amazon Redshift) for a clever implementation (but still something of a hack). This is a similar concept to Expanding JSON arrays to rows with SQL on RedShift.

The bottom line is that you can come up with some hacks that will work to a limited degree, but the best option is to clean the data before loading it into Amazon Redshift. At the moment, Redshift is optimized for extremely fast querying over massive amounts of data, but it is not fully-featured in terms of data manipulation. That will probably change in future (just like User Defined functions were not originally available) but for now we have to work within its current functionality.

Answer 2

Stealing from this answer Split column into multiple rows in Postgres

select product_id, p.option
from product_options po,
     unnest(string_to_array(po.options_id, ';')) p(option)

sqlfiddle