SQL：如何将相应位置的两个数组中的元素组合在一起

我需要将array1和array2组合在一起，但它们在相应位置的元素应该组合在一起.array1和array2的长度很高，并手动将它们与

ARRAY_CONSTRUCT(
      ARRAY_CONSTRUCT(array1[1], array2[1]),
      ARRAY_CONSTRUCT(array1[2], array2[2]),
      ARRAY_CONSTRUCT(array1[3], array2[3])
      ...
    )

是个糟糕的解决方案.对如何实现这一目标有什么建议吗？

样本数据:

CREATE OR REPLACE TEMPORARY TABLE sample_table AS (
  SELECT
    TO_DATE('2022-01-01', 'YYYY-MM-DD') AS date_column,
    ARRAY_CONSTRUCT(1, 2, 3) AS array1,
    ARRAY_CONSTRUCT(10, 20, 30) AS array2
  UNION
  SELECT
    TO_DATE('2022-01-02', 'YYYY-MM-DD') AS date_column,
    ARRAY_CONSTRUCT(4, 5) AS array1,
    ARRAY_CONSTRUCT(30, 40) AS array2
  UNION
  SELECT
    TO_DATE('2022-01-03', 'YYYY-MM-DD') AS date_column,
    ARRAY_CONSTRUCT(6, 7, 8, 9) AS array1,
    ARRAY_CONSTRUCT(60, 70, 80, 90) AS array2
);

预期结果:

CREATE OR REPLACE TEMPORARY TABLE sample_table_2 AS (
  SELECT
    TO_DATE('2022-01-01', 'YYYY-MM-DD') AS date_column,
    ARRAY_CONSTRUCT(1, 2, 3) AS array1,
    ARRAY_CONSTRUCT(10, 20, 30) AS array2,
    ARRAY_CONSTRUCT([1,10],[2,20],[3,30]) AS array3
  UNION
  SELECT
    TO_DATE('2022-01-02', 'YYYY-MM-DD') AS date_column,
    ARRAY_CONSTRUCT(4, 5) AS array1,
    ARRAY_CONSTRUCT(30, 40) AS array2,
    ARRAY_CONSTRUCT([4,30],[5,40]) AS array3
  UNION
  SELECT
    TO_DATE('2022-01-03', 'YYYY-MM-DD') AS date_column,
    ARRAY_CONSTRUCT(6, 7, 8, 9) AS array1,
    ARRAY_CONSTRUCT(60, 70, 80, 90) AS array2,
    ARRAY_CONSTRUCT([6,60],[7,70],[8,80],[9,90]) AS array3
)