User-Defined Function collection

Tutorials
,
1

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

Sorts an integer array ascending:

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

Usage:

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

Filter numeric arrays

Filters all elements in an array within a given range:

CREATE OR REPLACE FUNCTION array_filter(arr ARRAY(INTEGER), min_value INTEGER, max_value INTEGER)
RETURNS ARRAY(INTEGER)
LANGUAGE JAVASCRIPT
AS 'function array_filter(arr, min_value, max_value) { 
        return arr.filter(element => element >= min_value && element <= max_value); 
}';

Usage:

SELECT array_filter([5, 7, 20], 2, 8) 
-- Result: [5, 7]

Remove an element by index

Remove the element in a given position:

CREATE OR REPLACE FUNCTION array_remove_index(ARRAY(INTEGER), int)
RETURNS ARRAY(INTEGER)
LANGUAGE JAVASCRIPT
AS 'function array_remove_index(var_array,start) {
    var_array.splice(start,1);
    return var_array;
}';

Usage:

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

Remove elements by value

Remove every occurrence of an element with a given value:

CREATE OR REPLACE FUNCTION array_remove_values(ARRAY(INT), INT)
RETURNS ARRAY(INT)
LANGUAGE JAVASCRIPT
AS 'function array_remove_values(var_array,value) {
    indexof = var_array.indexOf(value);
    while(indexof != -1){
     var_array.splice(indexof,1);
     indexof = var_array.indexOf(value);
    }
    return var_array;
}';

Usage:

SELECT array_remove_values([1, 2, 2, 2, 3],2);
-- Result: [1, 3]

Vector Functions

Vector distance

Calculates the distance between two two-dimensional vectors:

CREATE OR REPLACE FUNCTION vector_distance(INTEGER, INTEGER, INTEGER, INTEGER)
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

Distance between points in an n-dimensional space

Calculates the distance between two points represented as arrays where the length of the arrays is the dimensionality of the space containing the points:

CREATE OR REPLACE FUNCTION n_dimensional_distance (p1 ARRAY(DOUBLE),p2 ARRAY(DOUBLE))
RETURNS DOUBLE
LANGUAGE JAVASCRIPT
AS
'	function n_dimensional_distance(p1,p2) {
		sum = 0;
		for (i = 0; i < p1.length; i++) { sum += Math.pow(p2[i] - p1[i], 2); }
		return Math.sqrt(sum);
	}';

Usage:

SELECT n_dimensional_distance([11,22,33],[44,55,66]);
-- Result: 57.15767664977295
3 Likes