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!
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)
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