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

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

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?

Solution

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;