BigQuery : is it possible to iterate over an array?

Question

Is it possible to iterate over an array in bigquery in standard sql?

Basically declare an array of strings representing table fields. ex :

DECLARE FIELDS_TO_CHECK ARRAY<STRING>;
SET FIELDS_TO_CHECK =  ['field1', 'field2', 'field3' ];

and then iterate on this array to create requests getting percentage of non null value on this field :

select count(FIELD) / count(*) from 
'table_name'`

Answer 1

Below is example for BigQuery Standard SQL
I am using here TEMP TABLE `table_name` to mimic your data with some simplistic dummy data, but you can just remove that CREATE statement and use your own table

#standardSQL
DECLARE FIELDS_TO_CHECK ARRAY<STRING>;
DECLARE i INT64 DEFAULT 0;

CREATE TEMP TABLE `table_name` AS 
  SELECT 1 field1, NULL field2, 3 field3, 4 field4, 5 field5 UNION ALL
  SELECT NULL, NULL, 3, NULL, 5 UNION ALL
  SELECT 1, NULL, 3, 4, 6;

CREATE TEMP TABLE result(field STRING, percentage FLOAT64);  
  
SET FIELDS_TO_CHECK =  ['field1', 'field2', 'field3' ];

LOOP
  SET i = i + 1;
  IF i > ARRAY_LENGTH(FIELDS_TO_CHECK) THEN 
    LEAVE; 
  END IF;
  EXECUTE IMMEDIATE '''
    INSERT result
    SELECT "''' || FIELDS_TO_CHECK[ORDINAL(i)] || '''", COUNT(''' || FIELDS_TO_CHECK[ORDINAL(i)] || ''') / COUNT(*) FROM `table_name`
  ''';

END LOOP; 

SELECT * FROM result;   

Above example returns below output

Row field   percentage   
1   field2  0.0  
2   field1  0.66666666666666663  
3   field3  1.0  

Answer 2

You could use for in to loop through array elements.
The key point is to use UNNEST to fetch element inside array.
The sentence would be a little bit more declarative than use loop with index moving.

DECLARE FIELDS_TO_CHECK ARRAY<STRING>;
SET FIELDS_TO_CHECK =  ['field1', 'field2', 'field3' ];

FOR field IN
  (SELECT * from UNNEST(FIELDS_TO_CHECK))
DO
  # do whatever you want with field
END FOR