My issue revolves around a peculiar behavior I encountered while working with a table that includes a repeated integer field named coord1:
https://i.sstatic.net/B5I6N.png
Initially, when attempting to compute the sum of the integer array coord1 in BigQuery, my code unexpectedly resulted in concatenating the values as strings instead of adding them up. Below is the code snippet along with its output:
CREATE TEMP FUNCTION func(array_values ARRAY<INT64>)
RETURNS INT64
LANGUAGE js AS """
var divisor = 0;
for(var i = 0; i < array_values.length; i++){
divisor = divisor + array_values[i];
};
return divisor;
""";
with
`project.dataset.table` as
(
SELECT 1 id1, [1, 2, 3, 4] coord1 UNION ALL
SELECT 2, [2, 0, 1, 1, 0, 2, 1, 1]
)
SELECT
id1,
coord1,
func(coord1) as array_sum
from
`project.dataset.table`
https://i.sstatic.net/jTMpe.png
Upon further investigation, I found that incorporating the parseInt function before executing the addition operation on the array elements produced the desired result:
CREATE TEMP FUNCTION func(array_values ARRAY<INT64>)
RETURNS INT64
LANGUAGE js AS """
var divisor = 0;
for(var i = 0; i < array_values.length; i++){
divisor = divisor + parseInt(array_values[i]);
};
return divisor;
""";
with
`project.dataset.table` as
(
SELECT 1 id1, [1, 2, 3, 4] coord1 UNION ALL
SELECT 2, [2, 0, 1, 1, 0, 2, 1, 1]
)
SELECT
id1,
coord1,
func(coord1) as array_sum
from
`project.dataset.table`
https://i.sstatic.net/Ze6NP.png
The question that arises now is: what led to the initial code treating array values as strings and performing string concatenation instead of summation?