CODEWITHSUNDEEP
sqlamazon-redshiftunnest
Makaroni
Makaroni

Reputation: 912

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!

Upvotes: 9

Views: 23150

Answers (4)

Jessy Neves
Jessy Neves

Reputation: 11

Here's this simple solution

-- unnest with REDSHIFT
create table test (
    event_name varchar,
    json_arr varchar
);

insert into test values
('hello', '["a", "b", "c"]'),
('goodbye', '["d", "e", "f"]');

with parsed as (
    select *, json_parse(json_arr) as parsed_account_ids from test
)

SELECT index, element, event_name FROM parsed AS b, b.parsed_account_ids AS element AT index;

Upvotes: 1

Denis Imamovic
Denis Imamovic

Reputation: 21

I played around to get the same result on array column using recursive CTE, here's something what I come up. I'm sure there is a better way but here it is anyway..

WITH recursive unnested(id, elem, idx) AS (
    SELECT
        id,
        arr_column [0] AS elem,
        0 AS idx
    FROM
        nest_column_table
    WHERE
        id = 1
    UNION
    ALL
    SELECT
        (
            CASE
                WHEN umi.idx + 2 >= get_array_length(ci.arr_column) THEN umi.id + 1
                ELSE umi.id
            END
        ) AS id,
        arr_column [umi.idx + 1] AS elem,
        (
            CASE
                WHEN umi.idx + 2 >= get_array_length(ci.arr_column) THEN -1
                ELSE umi.idx + 1
            END
        ) AS idx
    FROM
        nest_column_table ci
        INNER JOIN unnested umi ON umi.id = ci.id
)
SELECT
    *
FROM
    unnested;

Upvotes: 1

Svend
Svend

Reputation: 7208

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

Upvotes: 17

GMB
GMB

Reputation: 222582

What is the dataatype of that column?

Redshift does not support arrays, so let me assume this is a JSON string.

Redshift does not provide JSON set-returning functions: we need to unnest manually. Here is one way to do it, if you have a table with a sufficient numbers of rows (at least as many rows as there are elements in the array) - say sometable:

select json_extract_array_element_text(t.id, n.rn) as new_id
from mytable t
inner join (select row_number() over() - 1 as rn from sometable) n
    on n.rn < json_array_length(t.id)

Upvotes: 1

Related Questions