Filter BigQuery Based on Values Within an Array

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?


Note: Adding square brackets and quotes around the transformer removes the syntax error, however my query returns no results :frowning:

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?

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


Ah, actually, those examples should work for dynamically passed in arrays! You’d just replace [1,2,3] with your transformer.value

where id IN UNNEST({{ arrayTransformer.value }})

Should work :thinking: 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 ""
1 ""
2 ""

Is it because I don't have quotation marks in the array values?

Definitely not going crazy :sweat_smile:

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: