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 | +---------------------+

SQL Function Example

You can also call any SQL function from a JavaScript UDF.

script {{{
  
import * as sql from "sql";
      
export function tokenize_all(obj) {
	let r = {};
	for (const [k, v] of Object.entries(obj)) {
		if (v == null) {
			continue;
		}
		if (typeof(v) == "string") {
			r[k] = sql.tokenize(v);
		} else {
			r[k] = v;
		}
	}
	return r;
}

}}}

select _script.tokenize_all(
	{'foo': 1, 'bar': null, 'baz': 'hello world, goodbye'}
) x;

+---------------------------------------------+ | x | +---------------------------------------------+ | {"foo":1,"baz":["hello","world","goodbye"]} | +---------------------------------------------+

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])

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 | +-------+------+------+------+------+