Multiselect value in snowflake query doesn't work

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