7

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!

Svend
  • 6,352
  • 1
  • 25
  • 38
Makaroni
  • 880
  • 3
  • 15
  • 34
  • What is the dataatype of that column? – GMB Dec 24 '20 at 11:04
  • @GMB How to see that in redshift? – Makaroni Dec 24 '20 at 11:13
  • Have you tried something with unnest(ARRAY[...])? – ecp Dec 24 '20 at 11:24
  • @ecp Yup, for example just to see if it works, I tried `SELECT unnest(ARRAY[1,2])`, and it throws an error: `org.postgresql.util.PSQLException: ERROR: syntax error at or near "unnest" Position: 78` – Makaroni Dec 24 '20 at 11:32
  • @Makaroni . . . Redshift does not support arrays. Hence, the question doesn't really make sense and you need to explain what the column type is. – Gordon Linoff Dec 24 '20 at 12:59
  • Lets just assume the column type is VARCHAR and move on with a solution. OP already said not working with JSON. – Merlin Nov 01 '21 at 17:42

3 Answers3

7

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

Svend
  • 6,352
  • 1
  • 25
  • 38
2

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)
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Can a similar thing be constructed assuming the column type is not json but VARCHAR? `'[1,2,3...]'` delimited by commas? Maybe we can use `split_part` instead of json_extract_array_element_text. `split_part(t.id, ',', n.rn)` and also change json_array_length to get number of elements in varchar column. – Merlin Nov 01 '21 at 17:44
0

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;
Denis Imamovic
  • 11
  • 2