Copy Command Redshift putting quotes around super column values

Question

I created a table

create table test.array_test    
(
id integer,
super_field super
)

When I insert data rows like below

insert into test.array_test
values (2,'[1,2,3,10]');

insert into test.array_test
values (3,array(1,2,3,10));

When I run a select I get rows like below where id=2 is putting quotes around the outside of the array

id  super_field
--  -----------
2   "[1,2,3,10]"
3   [1,2,3,10]

I am able to unnest the id = 3 with below sql but am not getting any rows back for id = 2

select t.id,  t1
from   test.array_test t,
      t.super_field t1

id  t1
--  --
3   1
3   2
3   3
3   10

Is it possible to turn the id = 2 into an array without the quotes? The copy table command in redshift is putting the values into the table with quotes around them

Answer 1

In your example

insert into test.array_test
values (2,'[1,2,3,10]');

'[1,2,3,10]' is a single string. It is being stored as a single value in your super column.

You need to insert the super data you desire not just a string. The easiest way is

insert into test.array_test
values (2,JSON_PARSE('[1,2,3,10]'));

See https://docs.aws.amazon.com/redshift/latest/dg/ingest-super.html for more information on this process.

Answer 2

I used part of above as a basis then below to get the values from id = 2

select *
from  
    (
        select id,json_parse(super_field::text) super_field
        from   test.array_test
    ) arr,
    arr.super_field t1

I am only using insert to show example I am using redshift copy command to import the data so I don't have the option to put json_parse round values when inserting