Cratedb loop through array

CrateDB SQL
2

Hi @arafaraf and welcome to our community!

This can be solved with a User-Defined Function.

If you want to find the elements in the array, you could use a function like this:

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

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

If you only want to identify if there is a value within the given boundaries, you can also do this:

CREATE OR REPLACE FUNCTION array_find(ARRAY(INTEGER), INTEGER, INTEGER) RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS 'function array_find(array_integer, min_value, max_value) {
    return Array.prototype.find.call(array_integer, element => element >= min_value && element <= max_value) !== undefined;
}';

SELECT array_find([5, 7, 20], 5, 300);
-- returns true

SELECT array_find([5, 7, 20], 25, 300);
-- returns false

–
Best

Niklas