Correct way to pass query parameter in C# as argument to IN operator in BigQuery

user_275449 0
Viewed 641

I am looking to pass an array of strings as a query parameter to an IN operator. I have been struggling with this for a while now and nothing I try seems to work. I am wondering whether this is even possible to do.

I have tried the following syntax variants:

where state IN (@states)
where state IN @states
where state IN ARRAY(@states)
where state IN (ARRAY(@states))

And this is the C# end:

new BigQueryParameter("states", BigQueryDbType.Array, new[] {"AL", "CA"}),
new BigQueryParameter("states", null, new[] {"AL", "CA"}),

I get a variety of errors, depending on what I try:

  • No matching signature for operator IN for argument types STRING and {ARRAY< STRING>}
  • Syntax error: Expected "(" or keyword SELECT or keyword WITH but got "@"
  • Syntax error: Expected "(" or keyword UNNEST but got keyword ARRAY
  • Syntax error: Expected "(" or keyword UNNEST but got "@"
google-bigquery c#
1 Answers

Use the UNNEST operator like so:

SQL

where state IN UNNEST(@states)

C#

new BigQueryParameter("states", BigQueryDbType.Array, new[] {"AL", "CA"}) {ArrayElementType = BigQueryDbType.String},
user_5076990
Related