BigQuery UDF with n number of parameters

[phaneejv]

Hi Team,

How to create a udf in bigquery that takes, n number of parameters of different datatypes.

for example like JSON_OBJECT: https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_object

Please let us know 

[ms4446]

In BigQuery, to create a User-Defined Function (UDF) that accepts a variable number of parameters of different data types, you can use JavaScript UDFs. JavaScript UDFs allow you to work with flexible input and implement custom logic.

Below is an example UDF that mimics the behavior of JSON_OBJECT:

CREATE OR REPLACE FUNCTION `my_project.my_dataset.json_object_udf`(params ARRAY<STRUCT<key STRING, value ANY TYPE>>)
RETURNS STRING
LANGUAGE js AS 
"""
  const result = {};
  params.forEach(param => {
    result[param.key] = param.value; 
  });
  return JSON.stringify(result);
""";

Here's an example of how to use this UDF:

SELECT
  `my_project.my_dataset.json_object_udf`(ARRAY<STRUCT<key STRING, value ANY TYPE>>[
    ('first_name', 'John'),
    ('last_name', 'Doe'),
    ('age', 40),
    ('is_active', TRUE),
    ('account_balance', 1000.75)
  ]) AS json_result;

The output will be a JSON string:

{"first_name":"John","last_name":"Doe","age":40,"is_active":true,"account_balance":1000.75}

[marckevin]

Hi @phaneejv,

Welcome to Google Cloud Community!

In addition to @ms4446 solution, you can also try CREATE TEMP FUNCTION for a temporary function which can directly query the result and also uses JavaScript UDFs.

CREATE TEMP FUNCTION MULTIPARAMUDF(params STRING)
RETURNS STRING
LANGUAGE js AS """
 // Parse the JSON string into an object
 let parsedParams = JSON.parse(params);
 // Convert the parsed object into a JSON string
 return JSON.stringify(parsedParams);
""";


SELECT MULTIPARAMUDF('{"PARAMETER1": 42, "PARAMETER2": "example", "PARAMETER3": 3.14, "PARAMETER4": "SAMPLE UDF"}') AS RESULT;

Replicated Output

For more information on how to create a UDF, you can refer to this documentation.

I hope the above information is helpful.