Can a BigQuery UDF have a variable number of arguments (varargs)?

Is there a possible way to do something like this in BigQuery?

CREATE OR REPLACE FUNCTION `mydataset.variant_test.any_are_null`(val1 ANY TYPE, val2 ANY TYPE, ...) AS (
  val1 IS NULL OR val2 IS NULL OR ...
);

In other words, I want to be able to check if any columns/values are null in a concise way, something like:

SELECT any_are_null(col1, col2, col3, col4), any_are_null(col1, col2)

Without knowing the number of arguments beforehand. I suppose being able to zip/unzip the values would be one option as well (such as ... or * in some languages).

> I want to be able to check if any columns/values are null in a concise way

It is achievable by using built-in GREATEST:

> GREATEST(X1,...,XN)
>
> Returns the greatest value among X1,...,XN. If any argument is NULL, returns NULL.

Then any_are_null is effectively equivalent of:

SELECT GREATEST(col1, col2, col3, col4)
FROM tab;

or:

SELECT GREATEST(col1, col2, col3, col4) IS NULL
FROM tab;

SELECT 
   CASE WHEN GREATEST(col1, col2, col3, col4) IS NULL THEN 'At least one NULL value'
        ELSE 'No NULLs detected'
   END
FROM tab;

Looks like you've answered your own question - BigQuery doesn't provide variable argument lengths for UDFs (a.k.a. vargs / varargs).

Below is my attempt using an array instead (which would be used under the hood of varargs syntactic sugar anyway). Very similar to yours but with minor tweaks such as narrower typing (and reluctantly using a temporary function so it could be run in the Dekart BigQuery Playground):

CREATE TEMPORARY FUNCTION hasNullValue(arr ARRAY<INT64>)
RETURNS BOOLEAN
AS (
  EXISTS (
    SELECT value FROM UNNEST(arr) AS value WHERE value IS NULL
  )
);
SELECT hasNullValue([1, 2, NULL, 3]) AS contains_null;

If you are able to modify the values list into an array (which can be variable-length of course) then you can use something like the following to get it to work:

CREATE OR REPLACE FUNCTION `mydataset.variant_test.any_val_is_null`(arr ANY TYPE) AS (
  (SELECT EXISTS(SELECT 1 FROM UNNEST(arr) elem WHERE elem IS NULL LIMIT 1))
);

select variant_test.any_val_is_null([1,2,null,3]) as val union all
select variant_test.any_val_is_null([1,2]) union all
select variant_test.any_val_is_null([1,2,3,4,5,6,7,8])

Beyond consolidating values into an array (or struct or json, but it'd be more difficult), there's no way currently to pass variable-length arguments to a UDF in BigQuery.

You can use a combination of the ARRAY_AGG and ARRAY_LENGTH functions. The AnyAreNull function uses the ARRAY_AGG function to aggregate the non-null values in the array arr. It then compares the length of the aggregated array with the length of the original array. If the lengths are different, it means there was at least one NULL value in the original array.

    CREATE TEMP FUNCTION AnyAreNull(arr ANY TYPE) AS (
      (SELECT ARRAY_LENGTH(ARRAY_AGG(val)) < ARRAY_LENGTH(arr) 
       FROM UNNEST(arr) AS val)
    );

SELECT 
  AnyAreNull([col1, col2, col3, col4]) AS any_null1,
  AnyAreNull([col1, col2]) AS any_null2
FROM your_table;

I tried to create a temporary function called AnyAreNull, which takes an array as input and returns TRUE if any of the values in the array are NULL, and FALSE otherwise.

Inside the SELECT statement, I called the AnyAreNull function on different arrays of columns ([col1, col2, col3, col4] and [col1, col2]) to check for null values.

In this context, I used the UNNEST function to convert the input arrays into a table, allowing me to apply the COUNTIF function to check for NULL values.
.

You can use ARRAY_TO_STRING with delimiter and null_text(https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions#array_to_string), next use LIKE operator (https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#like_operator) to check if have two delimiter adjacent then we have null value in array