SELECT a loop over an Array type. BigQuery

Question

Is there any way to loop over an array column within SELECT clause in Google's BigQuery?. Using pseudo-sql:

-- This is some arbitrary complex logic which can be resume as: sum some product while some accumulator is less than a value
accumulator1 = 0
accumulator2 = 0
user_defined_loop =
  FOR (x, y) in array
    accumulator1 += x * y
    accumulator2 += y
    IF accumulator2 <= 5000
       CONTINUE
    ELSE 
       BREAK
    END IF
  RETURN accumulator2
  END;

WITH example AS (
    SELECT [(1,20), (3,40), (5,60)] as array_type_column
    UNION ALL 
    SELECT [(7,80), (9,100), (5,110)]
    UNION ALL 
    SELECT [(5,7), (30,40), (50,60)]
) 
SELECT user_defined_loop(array_type_column)
  FROM example

Thanks in advance

user_13473525 · Answer

Try JavaScrpt UDFs:

CREATE TEMP FUNCTION user_defined_loop(arr ARRAY>)
RETURNS INT64
LANGUAGE js
AS """
  var accumulator1 = 0
  var accumulator2 = 0
  for (const item of arr)
  {
    accumulator1 += parseInt(item.x) * parseInt(item.y)
    accumulator2 += parseInt(item.y)
    if (accumulator2 > 5000) break 
  }
  return accumulator2;
""";

WITH example AS (
    SELECT [(1,20), (3,40), (5,60)] as array_type_column
    UNION ALL 
    SELECT [(7,80), (9,100), (5,110)]
    UNION ALL 
    SELECT [(5,7), (30,40), (50,60)]
) 
SELECT user_defined_loop(array_type_column)
FROM example

SELECT a loop over an Array type. BigQuery

1 Answers