BigQuery UDF with loop

Question

I would like an example of a function in BigQuery (SQL), in order to see the syntax needed to create such a function: create a function in BigQuery, using SQL (not JavaScript), where internally an array and loop is created to iterate this array, adding the values ​​of the array and returning the resulting sum (int64).

Answer 1

Below is for BigQuery Standard SQL

example of a function in BigQuery (SQL) that "loops" through array's elements and produces sum of its elements

create temp function sum_array(a array<INT64>) as ((
  select sum(element) from unnest(a) element
));

Below is example of using such function with simplified dummy data

#standardSQL
create temp function sum_array(a array<INT64>) as ((
  select sum(element) from unnest(a) element
));
with `project.dataset.table` as (
  select 1 id, [1, 2, 3, 4, 5,6 ] arr union all
  select 2, [1, 3, 5, 7, 9] union all
  select 3, [2, 4] 
)
select id, sum_array(arr) sum_elements_of_array 
from `project.dataset.table`
order by id

with output