sqlamazon-redshiftunnest

Unnest an array in AWS Redshift

I have a table with column with lists like this:

id
[1,2,3,10]
[1]
[2,3,4,9]

The result I would like to have is a table with unlisted values like this:

id2
1
2
3
10
1
2
3
4
9

I have tried different solutions that I found on the web, aws documentation, SO solution, blog post, but without any luck because I have a list in column and not a json object. Any help is appreciated!

Solution

  • Update (2022): Redshift now supports arrays and allows to "unnest" them easily.

    The syntax is simply to have a FROM the_table AS the_table_alias, the_table_alias.the_array AS the_element_alias

    Here's an example with the data mentioned in the question:

    WITH
  -- some table with test data
  input_data as (
    SELECT array(1,2,3,10)  as  id
    union all
    SELECT  array(1)  as id
    union all
    SELECT  array(2,3,4,9)  as id
  ) 


SELECT 
  id2 
FROM 
  input_data AS ids, 
  ids.id AS id2

    Yields the expected:

    id2
---
1
2
3
4
9
1
2
3
10

    See here for more details and examples with deeper nesting levels: https://docs.aws.amazon.com/redshift/latest/dg/query-super.html