Cratedb loop over column Array and see if the values fall between X and Y

Question

Hey stack overflow so I have a column called itemPrices that is an Array of integers;

[43, 44, 55]

So I have an api that gives me two numbers, X and Y. I want to take those two numbers and compare it against the Array. If the number in the object falls within X and Y I would like to retrieve the contents. How would i do such a thing in crateDB?

Answer 1

CREATE OR REPLACE FUNCTION filter_item_price(ARRAY(REAL), INTEGER, INTEGER) RETURNS BOOLEAN
  LANGUAGE JAVASCRIPT
  AS 'function filter_item_price(array_integer, min_value, max_value) {
    if(array_integer === null || array_integer === undefined) {
      return false;
    }
    return array_integer.some(element => element >= min_value && element <= max_value);
  }';

SELECT "itemPrices"
FROM scp_service_transaction.transactions_v2
WHERE "tenantId" = 'aptos-denim'
  AND "retailLocationId" IN (161)
  AND array_find("itemPrices", 98, 100) limit 100;

Answer 2

This can also be solved by using the array_min(array) and array_max(array) scalar functions:

cr> CREATE TABLE t1 (arr ARRAY(INTEGER));                                                                                                                                                                                                           
CREATE OK, 1 row affected  (1.918 sec)

cr> INSERT INTO t1 (arr) VALUES ([43, 44, 45]), ([42, 22, 105]);                                                                                                                                                                                    
INSERT OK, 2 rows affected  (0.112 sec)

cr> SELECT arr FROM t1 WHERE array_min(arr) >= 43 AND array_max(arr) <= 45;                                                                                                                                                                         
+--------------+
| arr          |
+--------------+
| [43, 44, 45] |
+--------------+
SELECT 1 row in set (0.008 sec)

Answer 3

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