# Cratedb loop through array

**URL:** <https://community.cratedb.com/t/cratedb-loop-through-array/1062>\
**Category:** SQL\
**Created:** [March 24, 2022, 9:35pm UTC](https://community.cratedb.com/t/cratedb-loop-through-array/1062 "2022-03-24T21:35:17Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![arafaraf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arafaraf/32/540_2.png) [@arafaraf](https://community.cratedb.com/u/arafaraf)\
**Post date:** [March 24, 2022, 9:35pm UTC](https://community.cratedb.com/t/cratedb-loop-through-array/1062/1 "2022-03-24T21:35:17Z")

</div>

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

```auto
[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

---

<div class="post-metadata">

**Author:** ![hammerhead](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hammerhead/32/270_2.png) [@hammerhead](https://community.cratedb.com/u/hammerhead)\
**Post date:** [March 25, 2022, 6:50am UTC](https://community.cratedb.com/t/cratedb-loop-through-array/1062/2 "2022-03-25T06:50:51Z")

</div>

Hi @arafaraf and welcome to our community!

This can be solved with a [User-Defined Function](https://community.cratedb.com/t/a-collection-of-useful-user-defined-functions-udfs/773).

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

```sql
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:

```sql
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

---

<div class="post-metadata">

**Author:** ![arafaraf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arafaraf/32/540_2.png) [@arafaraf](https://community.cratedb.com/u/arafaraf)\
**Post date:** [March 25, 2022, 1:31pm UTC](https://community.cratedb.com/t/cratedb-loop-through-array/1062/3 "2022-03-25T13:31:46Z")

</div>

> [@hammerhead](#):
>
> If you want to find the elements in the array, you could use a function like this:
> 
> ```auto
> 
> ```

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.

```auto
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"
```

---

<div class="post-metadata">

**Author:** ![hammerhead](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hammerhead/32/270_2.png) [@hammerhead](https://community.cratedb.com/u/hammerhead)\
**Post date:** [March 25, 2022, 1:43pm UTC](https://community.cratedb.com/t/cratedb-loop-through-array/1062/4 "2022-03-25T13:43:23Z")

</div>

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:

```sql
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

```

---

<div class="post-metadata">

**Author:** ![arafaraf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arafaraf/32/540_2.png) [@arafaraf](https://community.cratedb.com/u/arafaraf)\
**Post date:** [March 25, 2022, 1:53pm UTC](https://community.cratedb.com/t/cratedb-loop-through-array/1062/5 "2022-03-25T13:53:14Z")

</div>

> [@hammerhead](#):
>
> ```auto
> 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)
> 
> ```

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.

---

<div class="post-metadata">

**Author:** ![hammerhead](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hammerhead/32/270_2.png) [@hammerhead](https://community.cratedb.com/u/hammerhead)\
**Post date:** [March 25, 2022, 1:57pm UTC](https://community.cratedb.com/t/cratedb-loop-through-array/1062/6 "2022-03-25T13:57:10Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![arafaraf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arafaraf/32/540_2.png) [@arafaraf](https://community.cratedb.com/u/arafaraf)\
**Post date:** [March 25, 2022, 2:07pm UTC](https://community.cratedb.com/t/cratedb-loop-through-array/1062/7 "2022-03-25T14:07:47Z")

</div>

```auto
"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

```

1. So basically I have the user pass me in min and max item prices X and Y range
2. I have a current query as such

```auto
SELECT "serializedTransaction", FROM scp_service_transaction.transactions_v2 WHERE "tenantId" = 'aptos-denim' AND "retailLocationId" IN (161)

```

1. I have a column called itemPrices with the prices of items, I want to see if those item prices fall within the range of X AND Y.  
4)So if the user passes in 42, 40 and I have an itemPrices column with values [16, 19, 41] I want to return that row. So the row would be {serizliedTransaction : JSON in TEXT format}.

Hope that Helps!

---

<div class="post-metadata">

**Author:** ![hammerhead](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hammerhead/32/270_2.png) [@hammerhead](https://community.cratedb.com/u/hammerhead)\
**Post date:** [March 25, 2022, 2:17pm UTC](https://community.cratedb.com/t/cratedb-loop-through-array/1062/8 "2022-03-25T14:17:32Z")

</div>

So then you would apply the `array_find` function to `itemPrices` like this?

```sql
SELECT "serializedTransaction"
FROM scp_service_transaction.transactions_v2
WHERE "tenantId" = 'aptos-denim'
  AND "retailLocationId" IN (161)
  AND array_find("itemPrices", 40, 42)

```

---

<div class="post-metadata">

**Author:** ![arafaraf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arafaraf/32/540_2.png) [@arafaraf](https://community.cratedb.com/u/arafaraf)\
**Post date:** [March 25, 2022, 2:21pm UTC](https://community.cratedb.com/t/cratedb-loop-through-array/1062/9 "2022-03-25T14:21:32Z")

</div>

Thank you so much for your answer. But the resulting query gives me an error.

 ![Screen Shot 2022-03-25 at 10.20.04 AM](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/13ab8786135654f0d67bbd7459b6ec0eef641596.png)

```auto
SQLParseException[line 7:1: mismatched input 'SELECT' expecting <EOF>]

```

---

<div class="post-metadata">

**Author:** ![hammerhead](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hammerhead/32/270_2.png) [@hammerhead](https://community.cratedb.com/u/hammerhead)\
**Post date:** [March 25, 2022, 2:28pm UTC](https://community.cratedb.com/t/cratedb-loop-through-array/1062/10 "2022-03-25T14:28:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![arafaraf](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arafaraf/32/540_2.png) [@arafaraf](https://community.cratedb.com/u/arafaraf)\
**Post date:** [March 25, 2022, 2:34pm UTC](https://community.cratedb.com/t/cratedb-loop-through-array/1062/11 "2022-03-25T14:34:06Z")

</div>

Thank you so much it worked! Your a lifesaver!

Thank you !