Iterate over an array of criteria within a Select query

App Building
1

This query attempts to match integer id's with the contents of an array. It does not throw an error, but it does not give any output either:
image

This query uses the integer values within the array and behaves as expected:

image
image487×854 39.2 KB

I have a sneaking suspicion that I will need to iterate over the contents of the array, but am unsure how to do this in SQL.

The other possibility is that there is another way to make the integer values of 'cid' compatible with the string content of the array.

As usual, assistance is much appreciated.

2

Why are you changing cid into a text type?

3

Because the array is a text string and cid is an integer primary key. If I don't re-cast the cid then it doesn't work and I get a malformed array error.

4

Yes I figured as much - I think the String can be modified by using JSON.parse...(maybe) but check out this post as it seems similar in that the result the user was getting from AI was a string (which yours seems to be instead of an Array) - if you can get the Array properly formatted you won't have to change the cid integer to text

1 Like
5

ScottR, you are indeed a 'steely-eyed missile man'.

For the sake of any other poor soul that finds this post I will endeavor to explain how I implemented your wisdom.

My use case involved creating an 'op' string in an onscreen table, with multiple row select enabled, that looked very much like an array (the values are the integer primary keys within a data table):

"[118639,118642,118644,118645,118641,118647,118640]"

I needed to extract the records selected by the operator in the table using the 'IN' or '= ANY' sql functions. The two screenshots below should be self explanatory. Interestingly Retool warns that 'IN' may not work and recommends '= ANY' instead, and thus it proved to be...

image

image
image463×901 41.7 KB

...and it all looks so simple!

2 Likes