Hi Everyone,
I've queried some data from one resource (Salesforce) and transformed the data so that it contains an array of values. I now need to query some data from a different resource based on the values I have received from Salesforce.
I've tried something like this:
SELECT * FROM myTable WHERE fieldName IN UNNEST({{arrayTransformer.Value}}) but I'm getting a Syntax error message saying unexpected comma
Any ideas what I'm doing wrong or how I can achieve what I need to?
Cheers,
Dylan
Note: Adding square brackets and quotes around the transformer removes the syntax error, however my query returns no results 
SELECT * FROM myTable WHERE fieldName IN UNNEST(['{{arrayTransformer.Value}}'])
I'm guessing this is adding the whole array into [0] of the new array within the UNNEST?
Note: Yep looks like the above syntax adds whole array as 1 value.
I have managed to get the query to work by doing the below, however this is not scalable as my query can return any number of values.
SELECT * FROM myTable WHERE fieldName IN UNNEST(['{{transformer2.value['0']}}','{{transformer2.value['1']}}'])
Does anyone know how to get the whole array to be input into the unnest without having to specify [0], [1] etc?
Hey @Dylan! What resource type are you using? Would these docs be helpful at all? https://docs.retool.com/docs/sql-cheatsheet#use-arrays-in-queries
Hey @victoria, I'm using y42 BigQuery
Unfortunately not, those docs help if I'm manually setting the array within the query but don't mention how I can add a variable (transformer value) into the where clause
Cheers,
Dylan
Ah, actually, those examples should work for dynamically passed in arrays! You’d just replace [1,2,3] with your transformer.value
select
*
from
users
where id IN UNNEST({{ arrayTransformer.value }})
Should work 
but I know that’s what you had originally and it was throwing the comma error. What is your arrayTransformer.value? A screenshot of the value expanded out in the left panel’s State tab would be super helpful!
Glad I'm not going completely crazy then haha!
So I have managed to get something working although it does seem like a dodgy workaround. I've basically assigned all the values from the data I pulled into one long string within the transformer and then filtered via array using '.split'
SELECT * FROM myTable WHERE fieldName IN UNNEST([{{transformer2.value.split(',')}}])
However I think using just the array would be the better approach. The value of my array before I updated the transformer was like this:
0 "test.com"
1 "test1.com"
2 "test3.com"
Is it because I don't have quotation marks in the array values?
Definitely not going crazy 
Both an array of strings and an array of numbers seem to be working for me!
I'd be happy to step into your app if that sounds alright with you. If so, sending your app URL to this email address would be perfect:
