How deal with Redshift lack of support for Arrays

Question

Redshift does not support Arrays, however my source database has several Array columns that I need in Redshift.

How should this field type be handled when trying to migrate it into Redshift?

Answer 1

Surround it with a specific character that never appears on this field values.

Example:

field = |value1|value2|value3| 

And when querying it you just do this:

where field like '%|value1|%'

Also have in mind that like queries are expensive and can decrease your cluster's perfomance.

Answer 2

To query array fields in a Redshift table you just need to JOIN your table with its array field.

EXAMPLE

Given a table of customers with relative orders as an array.

To select customer IDs and order ship dates for customers that have orders:

SELECT c.id, o.shipdate
FROM   spectrum.customers c, c.orders o 

For each customer c that has orders, the FROM clause returns one row for each order o of the customer c. That row combines the customer row c and the order row o. Then the SELECT clause keeps only the c.id and o.shipdate. The result is the following.

id|      shipdate
--|----------------------
1 |2018-03-01  11:59:59
1 |2018-03-01  09:10:00
3 |2018-03-02  08:02:15

Reference to: https://docs.aws.amazon.com/redshift/latest/dg/tutorial-query-nested-data-sqlextensions.html

Answer 3

While Redshift does not support arrays in the PostgreSQL-sense, it provides some JSON functions you might want to have a look at: http://docs.aws.amazon.com/redshift/latest/dg/json-functions.html

You can insert arrays into varchar columns:

create temporary table _test (col1 varchar(20));
insert into _test values ('[1,2,3]');

Then using json_extract_array_element_text() would yield:

db=# select json_extract_array_element_text(col1, 2) from _test;
 json_extract_array_element_text
---------------------------------
 3
(1 row)