Following the retool doc, I used a multiselect component to filter a table.
The table data is provided by a snowflake query. I added the where condition to include to multiselect values.
See here
Retool version 2.80.17
Hi @dusty-campanella-maxa! Snowflake's way of handling arrays is pretty unique.
Here's the syntax that works for me once I have prepared statements enabled on the resource (a requirement for arrays in Retool + Snowflake)!
https://docs.retool.com/docs/sql-in-retool#prepared-statements
SELECT
*
FROM
PUBLIC.USERS
WHERE
ARRAY_CONTAINS(ID::variant, SPLIT( {{[1,6].join()}}, ',') )
Hi @victoria, thanks for the reply.
However ARRAY_CONTAINS doesn't check for substrings match. What if I want to get the users whose name contains a certain substrings?
This is the exact use case for the LIKE ANY clause I wrote in my initial message.
Best,
Dusty
Hmm, gotcha. I'll poke around to see how we can get this to work. Snowflake + Retool has been a little tricky, in my experience at least. In the meantime, if this is a viable solution, would the Query JSON with SQL query type work for you? You can pull in all your data from Snowflake and then filter it/write SQL against it.
https://docs.retool.com/docs/querying-via-sql
Here's an example of a Query JSON with SQL query where I join a Mongo query with a Postgres query (on matching 'blarg' and 'name' columns, respectively)
One thing to note! Mongo queries return their .data as an array of objects (perfect for the Query JSON with SQL query type). Others (like Postgres) return an object of arrays, so we need to use the formatDataAsArray method to transform it.
Let me know if you have any questions about this special query type—I'd be happy to help answer any questions/
Hi @victoria , thanks for sharing this workaround.
Unfortunately it doesn't suit our use case. We need to use the contains() clause, but it doesn't seem to be compatible with SQL-99 (the AlaSQL version used in Retool as I understood)
Dusty
Ah, I'm not sure if there's a workaround for this query type + CONTAINS.
You can, however, write a JS function in a query transformer or a standalone transformer (in my screenshot). Something like this:
return {{formatDataAsArray(query7.data)}}.filter(obj => obj.email?.includes('<p>') || obj.email?.includes('d'))
PS the reason I'm using the chaining operator is because some of my emails are null, so I'm excluding those.
Let me know if this would work for you!
