Hey guys, how would I iterate through an array in cratedb. So I have values X and Y and I want to check if a value in my array is between X and Y.
so my row has an array field as such
[5, 7, 20]
For each row in my table I want to iterate through the array and for each item I want to see if it is between X and Y. If it is in between X and Y then select that row.
Thank you
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
so I have a column called ‘serializedtransaction’. If the array has a value between X and Y how would I return the ‘serializedTransaction’ for each row. I also want to add where filters for example.
SELECT "serializedTransaction" FROM scp_service_transaction.transactions_v2 WHERE "tenantId" = 'sometenant' AND "retailLocationId" IN (161)...the rest of the query to filter through the array"You can use User-Defined Functions in a WHERE or SELECT clause. The query below would filter in the WHERE clause for any rows that have an array in the required range, and then return either the filtered array or the original one:
SELECT array_filter("serializedTransaction", 5, 100), -- filtered values
serializedTransaction -- original array
FROM scp_service_transaction.transactions_v2
WHERE "tenantId" = 'sometenant'
AND "retailLocationId" IN (161)
AND array_find("serializedTransaction", 5, 100) -- evaluates to true if the array has at least one value within that range
Im sorry I must have not phrased my question carefully. Basically I have a json blob thats called serializedTransaction that I am trying to retrieve. The array is a column called itemPrices.
Can you provide the table structure (SHOW CREATE TABLE scp_service_transaction.transactions_v2)? And maybe also a (simplified) example row with the expected output that the query should produce, please?
"transactionId" TEXT
"tenantId" TEXT
"retailLocationId" TEXT
"deviceId" TEXT
"businessDayDate" TEXT
"transactionNumber" BIGINT
"startDateTime" TIMESTAMP WITH TIME ZONE
"endDateTime" TIMESTAMP WITH TIME ZONE
"transactionType" TEXT
"transactionSubTypes" TEXT_ARRAY
"closingState" TEXT
"tenderTypes" TEXT_ARRAY
"fulfillmentTypes" TEXT_ARRAY
"referenceNumber" TEXT
"itemLinesSearchableText" TEXT
"minItemUnitPrice" REAL
"maxItemUnitPrice" REAL
"transactionTotalAmount" REAL
"performingUserDisplayName" TEXT
"transactionStatus" OBJECT
"serializedTransaction" TEXT
"createdAt" TIMESTAMP WITH TIME ZONE
"updatedAt" TIMESTAMP WITH TIME ZONE
"itemPrices" REAL_ARRAY
SELECT "serializedTransaction", FROM scp_service_transaction.transactions_v2 WHERE "tenantId" = 'aptos-denim' AND "retailLocationId" IN (161)
Hope that Helps!
So then you would apply the array_find function to itemPrices like this?
SELECT "serializedTransaction"
FROM scp_service_transaction.transactions_v2
WHERE "tenantId" = 'aptos-denim'
AND "retailLocationId" IN (161)
AND array_find("itemPrices", 40, 42)
Thank you so much for your answer. But the resulting query gives me an error.
SQLParseException[line 7:1: mismatched input 'SELECT' expecting <EOF>]
The SQL Editor in the Admin UI unfortunately doesn’t support running multiple statements. Try running them separately, so first only the CREATE OR REPLACE FUNCTION ... statement, and then only the SELECT statement.
Thank you so much it worked! Your a lifesaver!
Thank you !