Объединяйте несколько столбцов в массив только тогда, когда столбцы имеют ненулевое значение в Bigquery.

Ниже не является универсальным решением, но работает для вашего конкретного примера, который вы предоставили - идентификатор представлен буквенно-цифровым (не начинается с цифры), а остальные столбцы являются числами - целыми числами

#standardSQL
SELECT id,
  ARRAY(SELECT * FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r':(\d*)')) col WHERE col != '') AS agg_col_as_array,
  CONCAT('[', ARRAY_TO_STRING(ARRAY(SELECT * FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r':(\d*)')) col WHERE col != ''), ','), ']') AS agg_col_as_string
FROM `project.dataset.table` t

Вы можете протестировать, поиграть с приведенным выше, используя образцы данных из вашего вопроса, как показано ниже.

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'a' id, 1 col1, NULL col2, NULL col3, NULL col4, NULL col5 UNION ALL
  SELECT 'b', 1, 2, 3, 4, NULL UNION ALL
  SELECT 'c', 1, 2, 3, 4, 5 UNION ALL
  SELECT 'd', 2, 1, 7, NULL, 4
)
SELECT id,
  ARRAY(SELECT * FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r':(\d*)')) col WHERE col != '') AS agg_col_as_array,
  CONCAT('[', ARRAY_TO_STRING(ARRAY(SELECT * FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r':(\d*)')) col WHERE col != ''), ','), ']') AS agg_col_as_string
FROM `project.dataset.table` t
-- ORDER BY id   

с результатом как

Row id  agg_col_as_array    agg_col_as_string    
1   a   1                   [1]  
2   b   1                   [1,2,3,4]    
        2        
        3        
        4        
3   c   1                   [1,2,3,4,5]  
        2        
        3        
        4        
        5        
4   d   2                   [2,1,7,4]    
        1        
        7        
        4        

Как вы думаете, можно ли это сделать, указав определенные столбцы и затем связав их в массив?

Конечно, это выполнимо - см. Ниже

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'a' id, 1 col1, NULL col2, NULL col3, NULL col4, NULL col5 UNION ALL
  SELECT 'b', 1, 2, 3, 4, NULL UNION ALL
  SELECT 'c', 1, 2, 3, 4, 5 UNION ALL
  SELECT 'd', 2, 1, 7, NULL, 4
)
SELECT id, 
  ARRAY(
    SELECT col 
    FROM UNNEST([col1, col2, col3, col4, col5]) col 
    WHERE NOT col IS NULL
  ) AS agg_col_as_array,
CONCAT('[', ARRAY_TO_STRING(
  ARRAY(
    SELECT CAST(col AS STRING) 
    FROM UNNEST([col1, col2, col3, col4, col5]) col 
    WHERE NOT col IS NULL
  ), ','), ']') AS agg_col_as_string
FROM `project.dataset.table` t
-- ORDER BY id       

НО ... это не лучший вариант, так как вам нужно управлять и настраивать количество и имена столбцов в каждом случае для разных целей.

Ниже приводится скорректированная версия моего первоначального ответа на ваш последний комментарий - Actually the sample was too simple. Both of my id and other columns have alphanumeric and special characters.

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'a' id, 1 col1, NULL col2, NULL col3, NULL col4, NULL col5 UNION ALL
  SELECT 'b', 1, 2, 3, 4, NULL UNION ALL
  SELECT 'c', 1, 2, 3, 4, 5 UNION ALL
  SELECT 'd', 2, 1, 7, NULL, 4
)
SELECT id,
  ARRAY(
    SELECT col 
    FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r':(.*?)(?:,|})')) col WITH OFFSET 
    WHERE col != 'null' AND OFFSET > 0
  ) AS agg_col_as_array,
  CONCAT('[', ARRAY_TO_STRING(
    ARRAY(
      SELECT col 
      FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r':(.*?)(?:,|})')) col WITH OFFSET 
      WHERE col != 'null' AND OFFSET > 0
    ), ','), ']') AS agg_col_as_string
FROM `project.dataset.table` t
-- ORDER BY id   

оба с тем же результатом, что и раньше

Row id  agg_col_as_array    agg_col_as_string    
1   a   1                   [1]  
2   b   1                   [1,2,3,4]    
        2        
        3        
        4        
3   c   1                   [1,2,3,4,5]  
        2        
        3        
        4        
        5        
4   d   2                   [2,1,7,4]    
        1        
        7        
        4