A collection of useful User-Defined Functions (UDFs)

CrateDB supports User-Defined Functions to extend the functionality provided out of the box with custom functionality.

Feel free to use these UDFs for your own use case or as a starting point for creating your own UDFs

Array functions

Sort numeric arrays

CREATE OR REPLACE FUNCTION array_sort_asc (ARRAY(INTEGER))
RETURNS ARRAY(INTEGER)
LANGUAGE JAVASCRIPT
AS 'function array_sort_asc(array_integer) {
    return Array.prototype.sort.call(array_integer, (a, b) => a - b);
}';

Usage:

SELECT array_sort_asc([200, 1, 8]);
-- Result: [1, 8, 200]

Slice array

CREATE or replace FUNCTION array_slice(ARRAY(INT), INT, INT)
RETURNS ARRAY(INT)
LANGUAGE JAVASCRIPT
AS 'function array_slice(arr, start, stop) { 
    return Array.prototype.slice.call(arr, start, stop);
}';

Usage:

SELECT array_slice([1, 2, 3, 4, 5], 1, 4);
-- Result: 2, 3, 4

Vector functions

Vector Distance

CREATE OR REPLACE FUNCTION vector_distance (INT, INT, INT, INT)
RETURNS DOUBLE
LANGUAGE JAVASCRIPT
AS 'function vector_distance(x1, y1, x2, y2) {
  return Math.sqrt(
      Math.pow((x2 - x1), 2) + Math.pow((y2 - y1), 2)
  );
}';

Usage:

SELECT vector_distance(1,3,4,5);
-- Result: 3.6055