Assuming a source table with structure:
personid INT,
name STRING,
mobilephone STRING,
homephone STRING
and target table structure:
personid INT,
telephone ARRAY<STRING>
To insert into target from source, I did
select
personid,
array(mobilephone, homephone)
from source_table
12, ["+1958523366","+1258523366"]
13, ["+1958523366",""]
Second row in result shows empty string because homephone is null in source table. How to return something like this instead:
12, ["+1958523366","+1258523366"]
13, ["+1958523366"]
i.e. return item in array only if not null