Alternative of Array_Construct in Snowflake?

Question

I have a table for example:

ENO                 PHONELIST                         SHOTS
 1       [ ('1112223333','6195551234') ]            [ (11,12) ]
 2       [ ('1234556778','2348299494') ]            [ (13,14) ]

I need a SQL query that performs the following function:

COPY INTO Reporting_Table FROM (SELECT ( //columns that are not of array datatype) , 
ARRAY_CONSTRUCT( //columns that are of array datatype) ) FROM Staging_table;

Is there any way to do this?

Answer 1

Given the "arrays" you are showing have parens it makes me think you array data is not actually an array.

SELECT 
    array_construct(11,12) as a_real_array,
    '(13,14)' as str,
    array_construct(str) as a_fake_array,
    str::array as another_fake_array;

A_REAL_ARRAY	STR	A_FAKE_ARRAY	ANOTHER_FAKE_ARRAY
[ 11, 12 ]	(13,14)	[ "(13,14)" ]	[ "(13,14)" ]

given you results look like my fake results it appears you might be using ARRAY_CONSTRUCT wrong.

But maybe there is hope that you can just cast the value to ::ARRAY and it looks the same. But really if you are just pushing one value into an array, it is not an array, because there is only one value in there, and then you are having to process the sub-array later. If this is the case why not just store a STRING or VARIANT and process it later?