We've reorganized our documentation navigation structure to align directly with your operational workflows. See the release notes and the walkthrough video for more information.

User-defined functions

GoogleSQL for SecOps supports user-defined functions (UDFs).

A UDF lets you create a function using another SQL expression or another programming language, such as JavaScript or Lua. These functions accept columns of input and perform actions, returning the result of those actions as a value.

UDFs are temporary. This means you can only use them for the current query or command-line session.

SQL UDFs

A SQL user-defined function (UDF) operates on one row at a time and returns the result of that calculation as a single value.

All of the arguments are expressions that are computed in the context of a single row.

Create a SQL UDF

You can create a SQL UDF using the following syntax:

CREATE
  [ OR REPLACE ]
  { TEMPORARY | TEMP } FUNCTION
  [ IF NOT EXISTS ]
  function_name ( [ function_parameter [, ...] ] )
  [ RETURNS data_type ]
  AS ( function_body )

function_parameter:
  parameter_name
  data_type

This syntax consists of the following components:

CREATE ... FUNCTION: Creates a new function. A function can have zero or more function parameters.
- TEMPORARY or TEMP: Indicates that the function is temporary, meaning that it exists for the lifetime of the session. A temporary function can have the same name as a built-in function. If this happens, the temporary function hides the built-in function for the duration of the temporary function's lifetime.
OR REPLACE: Replaces any function with the same name if it exists. Can't appear with IF NOT EXISTS.
IF NOT EXISTS: If any function exists with the same name, the CREATE statement has no effect. Can't appear with OR REPLACE.
function_name: The name of the function.
function_parameter: A parameter for the function.
- parameter_name: The name of the function parameter.
- data_type: A GoogleSQL data type.
RETURNS data_type: Optional clause that specifies the data type that the function returns. GoogleSQL infers the result type of the function from the SQL function body when the RETURN clause is omitted.
function_body: The SQL expression that defines the function body.

You can create a public or privately-scoped UDF in a module. To learn more, see Modules.

Call a SQL UDF

You can call a SQL UDF in the same way that you call a built-in function. For details, see Function calls.

SQL UDF examples

The following example shows a UDF that employs a SQL function.

CREATE TEMP FUNCTION AddFourAndDivide(x INT64, y INT64)
RETURNS FLOAT64
AS (
  (x + 4) / y
);

WITH
  numbers AS (
    SELECT 1 AS val UNION ALL
    SELECT 3 AS val UNION ALL
    SELECT 4 AS val UNION ALL
    SELECT 5 AS val
  )
SELECT val, AddFourAndDivide(val, 2) AS result
FROM numbers;

/*-----+--------+
 | val | result |
 +-----+--------+
 | 1   | 2.5    |
 | 3   | 3.5    |
 | 4   | 4      |
 | 5   | 4.5    |
 +-----+--------*/

The following example shows a SQL UDF that uses the templated function parameter, ANY TYPE. The resulting function accepts arguments of various types.

CREATE TEMP FUNCTION AddFourAndDivideAny(x ANY TYPE, y ANY TYPE)
AS (
  (x + 4) / y
);

SELECT
  AddFourAndDivideAny(3, 4) AS integer_input,
  AddFourAndDivideAny(1.59, 3.14) AS floating_point_input;

/*----------------+-----------------------+
 | integer_input  | floating_point_input  |
 +----------------+-----------------------+
 | 1.75           | 1.7802547770700636    |
 +----------------+-----------------------*/

The following example shows a SQL UDF that uses the templated function parameter, ANY TYPE, to return the last element of an array of any type.

CREATE TEMP FUNCTION LastArrayElement(arr ANY TYPE)
AS (
  arr[ORDINAL(ARRAY_LENGTH(arr))]
);

SELECT
  names[OFFSET(0)] AS first_name,
  LastArrayElement(names) AS last_name
FROM
  (
    SELECT ['Fred', 'McFeely', 'Rogers'] AS names UNION ALL
    SELECT ['Marie', 'Skłodowska', 'Curie']
  );

/*------------+-----------+
 | first_name | last_name |
 +------------+-----------+
 | Fred       | Rogers    |
 | Marie      | Curie     |
 +------------+-----------*/

JavaScript UDFs

A JavaScript user-defined function (UDF) runs JavaScript code and returns the result as a single value.

Create a JavaScript UDF

You can create a JavaScript UDF using the following syntax:

CREATE
  { TEMPORARY | TEMP } FUNCTION
  function_name ( [ function_parameter [, ...] ] )
  RETURNS data_type
  LANGUAGE js AS function_body

function_parameter:
  parameter_name
  data_type

This syntax consists of the following components:

CREATE ... FUNCTION: Creates a new function. A function can have zero or more function parameters.
- TEMPORARY or TEMP: Indicates that the function is temporary, meaning that it exists for the lifetime of the session. A temporary function can have the same name as a built-in function. If this happens, the temporary function hides the built-in function for the duration of the temporary function's lifetime.
function_name: The name of the function.
function_parameter: A parameter for the function.
- parameter_name: The name of the function parameter.
- data_type: A GoogleSQL data type. See SQL type encodings in JavaScript to learn how GoogleSQL represents JavaScript types.
RETURNS data_type: Specifies the GoogleSQL data type that the function returns.
function_body: Quoted string literal that represents the JavaScript code that defines the function body. To learn more about the different types of quoted string literals you can use, see Formats for quoted literals.

You can create a public or privately-scoped UDF in a module. To learn more, see Modules.

Call a JavaScript UDF

You can call a JavaScript UDF the same way that you call a built-in function. For details, see Function calls.

SQL type encodings in JavaScript

GoogleSQL data types represent JavaScript data types as follows:

GoogleSQL data type	JavaScript data type	Notes
ARRAY	Array	An array of arrays isn't supported. To get around this limitation, use JavaScript `Array<Object<Array>>` and GoogleSQL `ARRAY<STRUCT<ARRAY>>`.
BOOL	Boolean
BYTES	String	Base64-encoded String.
FLOAT64	Number
NUMERIC	Number or String	If a NUMERIC value can be represented exactly as an IEEE 754 floating-point value and has no fractional part, it's encoded as a Number. These values are in the range [-2⁵³, 2⁵³]. Otherwise, it's encoded as a String.
INT64	String
STRING	String
STRUCT	Object	Object where each STRUCT field is a named property in the Object. Unnamed field in STRUCT isn't supported.
TIMESTAMP	Date object	See the documentation for your database engine.
DATE	Date object

Some GoogleSQL types have a direct mapping to JavaScript types, but others don't.

For example, because JavaScript doesn't support a 64-bit integer type, INT64 is unsupported as an input type for JavaScript UDFs. Instead, use FLOAT64 to represent integer values as a number, or STRING to represent integer values as a string.

GoogleSQL does support INT64 as a return type in JavaScript UDFs. In this case, the JavaScript function body can return either a JavaScript Number or a String. GoogleSQL then converts either of these types to INT64.

In addition, some GoogleSQL and JavaScript data types have different rules. For example, in JavaScript, you can have an array of arrays (Array<Array>), whereas in GoogleSQL, you can't. Before using encodings, ensure they are compatible. To learn more about GoogleSQL data types, see GoogleSQL data types. To learn more about JavaScript data types, see JavaScript data types.

JavaScript UDF examples

The following example illustrates a simple JavaScript UDF with a regular expression. Because there is a regular expression in the function body, the function body needs to be a raw string.

CREATE TEMP FUNCTION ExtractLetters(x STRING)
RETURNS STRING
LANGUAGE js
AS r'''
var re = /[a-z]/g;
return x.match(re);
''';

SELECT val, ExtractLetters(val) AS result
FROM UNNEST(['ab-c', 'd_e', '!']) AS val;

/*-------*---------+
 | val   | result  |
 +-------+---------+
 | ab-c  | [a,b,c] |
 | d_e   | [d,e]   |
 | !     | NULL    |
 +-------*---------*/

The following example illustrates a single-statement JavaScript UDF. Because the function body doesn't contain escape sequences or regular expressions, it can be a quoted or triple-quoted string literal.

CREATE TEMP FUNCTION PlusOne(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS 'return x+1;';

SELECT val, PlusOne(val) AS result
FROM UNNEST([1, 2, 3]) AS val;

/*-----------+-----------+
 | val       | result    |
 +-----------+-----------+
 | 1         | 2         |
 | 2         | 3         |
 | 3         | 4         |
 +-----------+-----------*/

The following example illustrates a more complex multi-statement JavaScript UDF.

CREATE TEMP FUNCTION CustomGreeting(a STRING)
RETURNS STRING
LANGUAGE js
AS r'''
  var d = new Date();
  if (d.getHours() < 12) {
    return 'Good Morning, ' + a + '!';
  } else {
    return 'Good Evening, ' + a + '!';
  }
''';

SELECT CustomGreeting(names) as everyone
FROM UNNEST(["Hannah", "Max", "Jakob"]) AS names;

/*-----------------------+
 | everyone              |
 +-----------------------+
 | Good Morning, Hannah! |
 | Good Morning, Max!    |
 | Good Morning, Jakob!  |
 +-----------------------*/

The following example creates a temporary JavaScript UDF.

CREATE TEMP FUNCTION MultiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''
  return x*y;
''';

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x, y, MultiplyInputs(x, y) as product
FROM numbers;

/*-----+-----+--------------+
 | x   | y   | product      |
 +-----+-----+--------------+
 | 1   | 5   | 5            |
 | 2   | 10  | 20           |
 | 3   | 15  | 45           |
 +-----+-----+--------------*/

You can create multiple JavaScript UDFs before a query. For example:

CREATE TEMP FUNCTION MultiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''
  return x*y;
''';

CREATE TEMP FUNCTION DivideByTwo(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''
  return x / 2;
''';

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x,
  y,
  MultiplyInputs(x, y) as product,
  DivideByTwo(x) as half_x,
  DivideByTwo(y) as half_y
FROM numbers;

/*-----+-----+--------------+--------+--------+
 | x   | y   | product      | half_x | half_y |
 +-----+-----+--------------+--------+--------+
 | 1   | 5   | 5            | 0.5    | 2.5    |
 | 2   | 10  | 20           | 1      | 5      |
 | 3   | 15  | 45           | 1.5    | 7.5    |
 +-----+-----+--------------+--------+--------*/

You can pass the result of a JavaScript UDF as input to another UDF. For example:

CREATE TEMP FUNCTION MultiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''
  return x*y;
''';

CREATE TEMP FUNCTION DivideByTwo(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r'''
  return x/2;
''';

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x,
  y,
  MultiplyInputs(DivideByTwo(x), DivideByTwo(y)) as half_product
FROM numbers;

/*-----+-----+--------------+
 | x   | y   | half_product |
 +-----+-----+--------------+
 | 1   | 5   | 1.25         |
 | 2   | 10  | 5            |
 | 3   | 15  | 11.25        |
 +-----+-----+--------------*/

The following example sums the values of all fields named foo in the given JSON string.

CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
RETURNS FLOAT64
LANGUAGE js
AS r'''
function SumFoo(obj) {
  var sum = 0;
  for (var field in obj) {
    if (obj.hasOwnProperty(field) && obj[field] != null) {
      if (typeof obj[field] == "object") {
        sum += SumFoo(obj[field]);
      } else if (field == "foo") {
        sum += obj[field];
      }
    }
  }
  return sum;
}
var row = JSON.parse(json_row);
return SumFoo(row);
''';

WITH
  Input AS (
    SELECT
      STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s,
      10 AS foo
    UNION ALL
    SELECT NULL, 4 AS foo
    UNION ALL
    SELECT
      STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s,
      NULL AS foo
  )
SELECT
  TO_JSON_STRING(t) AS json_row,
  SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sum
FROM Input AS t;

/*---------------------------------------------------------------------+---------+
 | json_row                                                            | foo_sum |
 +---------------------------------------------------------------------+---------+
 | {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10}       | 14.14   |
 | {"s":null,"foo":4}                                                  | 4       |
 | {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59    |
 +---------------------------------------------------------------------+---------*/