User-Defined Functions

JavaScript UDFs (user-defined functions) allow you to write JavaScript functions and invoke them from your SQL query. They come in handy when you want to perform certain operations on your data and the set of built-in SQL functions do not meet your needs.

Two types of JavaScript UDFs are supported:

1. Scalar JavaScript UDFs: these are vanilla JavaScript functions that return a single value. These are the most common ones.
2. Tabular JavaScript UDFs: these are JavaScript functions that return iterables and can be used as SQL table functions that produce rows. These UDFs can only be used as part of the FROM clause in your SQL query.

JavaScript UDFs are supported in your SQL queries, Query Lambdas, and SQL ingest transformation. They are not supported in Views just yet. JavaScript UDFs to define custom Aggregate or Window functions are not supported yet. JavaScript UDFs are only available on dedicated Virtual Instances.

Syntax

Simply wrap your JavaScript functions within the script {{{ <your JavaScript code> }}} tag and then continue constructing your SQL query as usual. The JavaScript functions that you’d like to use in SQL must be exported as if your script was a JavaScript module.

script {{{
<your JavaScript code>
}}}

SELECT ...

Hello World Example

script {{{
export function helloworld(name) {
    return 'hello, ' + name
}
}}}

SELECT _script.helloworld('ferro');

The above code will return:

hello, ferro

Note that, while JavaScript can use both single and double quotes for strings (’hello’ and "hello" would both work), Rockset SQL only allows single quotes (’ferro’, NOT "ferro").

Data Types

Input Parameter Types

When parameters are passed into JavaScript UDFs, Rockset values will be converted to JavaScript values according to their types, as follows:

Special Properties for Input Parameters

You can optionally do the following data type conversions on the JavaScript UDF input parameters by setting certain properties on the function definition:

• int64 →BigInt
  • Set the property input_convert_int64_to_bigint = true
• object → Map
  • Set the property input_convert_object_to_map = true For example:

script {{{

export function echo(n) { return n }

echo.input_convert_int64_to_bigint = true

}}}

SELECT _script.echo(9223372036854775807);

The above function will return the correct value 9223372036854775807 since the Rockset datatype will get mapped to BigInt.

If input_convert_int64_to_bigint was not set to true, then the SQL above will return 9223372036854776000 instead, since JavaScript numbers lose precision beyond 2^53.

Output Return Types

JavaScript values returned by the UDF will be converted into Rockset values according to their types, as follows:

Special Properties for Output Return Types

You can optionally do the following data type conversions on the JavaScript UDF return values by setting certain properties on the function definition:

• number -> float
  • Set the property output_always_convert_number_to_float = true

For Example:

script {{{

export function echo(n) { return n }

echo.output_always_convert_number_to_float = true

}}}

SELECT typeof(_script.echo(10));

The above function will return float. But if output_always_convert_number_to_float was not set to true, then it will return int.

Tabular JavaScript UDFs

These are JavaScript functions that return iterables and can be used as SQL table functions that produce rows. These UDFs can only be used as part of the FROM clause in your SQL query. While not required, it is very convenient to use generator functions to define tabular UDFs. The iterable must yield an object of {field: value, ...} for every row.

Hello Worlds Example

script {{{

// Note "function*" which defines a generator function
export function* helloworlds(names) {
    for (const name of names) {
        yield {"greeting": "hello, " + name};
     }
}

}}}

SELECT u.greeting
FROM   _script.helloworlds(['ferro', 'ferret', 'rocks-d-bear']) u;

+---------------------+
| greeting            | 
|---------------------+
| hello, ferro        |
| hello, ferret       |
| hello, rocks-d-bear |
+---------------------+

More Examples

ARRAY_SUM

Sum all numeric values in the given input array.

script{{{

export function array_sum(arr) {
    let sum = 0;
    for (var i in arr) {
      sum += arr[i];
    }
    return sum;
}

}}}

SELECT _script.array_sum([1, 2, 3, 4])

10

Fibonacci

Return the nth number in the Fibonacci sequence.

script {{{

function _fib(n, a, b) {
  if (n == 0) {
    return a
  }
  return _fib(n-1, b, a+b)
}

export function fibonacci(n) {
  return _fib(n, 0, 1)
}

}}}

SELECT i, _script.fibonacci(i) as fibo
FROM   UNNEST(SEQUENCE(0, 10) as i) u;

+-------+-------+
| i     | fibo  |
|-------|-------|
|0      | 0     |
|1	| 1     |
|2	| 1     |
|3	| 2     |
|4  	| 3     |
|5	| 5     |
|6	| 8     |
|7	| 13    |
|8	| 21    |
|9	| 34    |
|10     | 55    |
+-------+-------+

Transpose

Build a table function that takes an array of objects as input, transposes the rows and columns and then returns a row for every input column in the original input.

script {{{

export function* transpose(rows, pivot_column, new_column) {
    // transpose all rows and columns into a 2D map
    let rows_map = {}
    for (var i in rows) {
      let istr = '' + i
      for (const k in rows[i]) {
      	if (!(k in rows_map)) {
          rows_map[k] = new Object()
        }
        rows_map[k][istr] = rows[i][k]
	}
    }

	  // return a row for every column in the original rows_map
    for (const col in rows_map) {
      if (col === pivot_column) {
        // no need to produce a row for the pivot column
        continue
      }

      // build a new row to return
      let transposed_row = {}
      // set value for new column
      transposed_row[new_column] = col

      // set all other fields
      for (const istr in rows_map[col]) {
        let column_name = rows_map[pivot_column][istr]
        let column_value = rows_map[col][istr]
        transposed_row[column_name] = column_value
      }
      yield transposed_row
    }
    
}

}}}


WITH _sample as (
  SELECT [
    {'SCENARIO': 'S1', 'RED': 100, 'BLUE': 110, 'GREEN': 95},
    {'SCENARIO': 'S2', 'RED': 550, 'BLUE': 400, 'GREEN': 350},
    {'SCENARIO': 'S3', 'RED': 211, 'BLUE': 110, 'GREEN': 295},
    {'SCENARIO': 'S4', 'RED': 950, 'BLUE': 950, 'GREEN': 950},
  ] as rows
)

SELECT 
    u.*
FROM 
    _sample as s,
    _script.transpose(s.rows, 'SCENARIO', 'COLOR') u;

/* 
-- Use the following SQL to return the rows in original form for testing
SELECT 
    u.*
FROM 
    _sample as s,
    unnest(s.rows) u;
 */

+-------+------+------+------+------+
|COLOR	| S1   | S2   | S4   | S3   |
|-------|------|------|------|------|
|BLUE	| 110  | 400  | 950  | 110  |
|GREEN	| 95   | 350  | 950  | 295  |
|RED	| 100  | 550  | 950  | 211  |
+-------+------+------+------+------+

Security

JavaScript UDFs run in an isolated and sandboxed environment for security purposes.

• Each query executes its own script functions and cannot access anything from outside its isolated environment.
• JavaScript UDFs do not have access to the network or the filesystem.
• JavaScript UDFs are supported only on dedicated Virtual Instances and will not be available on shared or free Virtual Instances.
• All code generation functionality is disabled (eval, the Function constructor, etc).
• JavaScript UDFs do not have access to any timing facility. Date.now() returns the same value for the entire duration of the query, which is the same value that CURRENT_TIMESTAMP() would return in SQL.

Known Limitations

JavaScript UDFs currently have the following limitations:

• They can only be used on Dedicated VIs.
• They can’t be saved as first class entities in Rockset, and must be specified in the script section of the query every time the query is executed.
• They cannot be shared within or across workspaces.
• They can’t be used in View definitions.
• You can’t use JavaScript to define custom Aggregate or Window functions.