No matching signature for operator IN for argument types STRING and {ARRAY<STRING>} (Google BigQuery)

Question

I'm using Google BigQuery Ruby Client v0.23 and trying to use parameterized queries. I'm following API docs for reference.

When I run queries without the params, all is fine. However to make them dynamic, when I use an array params I get error. E.g. When I run this query

bigquery.query("SELECT COUNT(*) FROM oven.sensor_counts WHERE _PARTITIONTIME = TIMESTAMP('2016-04-04') AND sensor_id IN (@sensor_ids)", params: { sensor_ids: ['48-6', '48-2'] })

I get this

#<Harley::Response POST https://www.googleapis.com/bigquery/v2/projects/sensors-160421/queries == 400 (413 bytes) 3458ms>
Caught error invalidQuery: No matching signature for operator IN for argument types STRING and {ARRAY<STRING>} at [1:116]
Error - #<Google::Apis::ClientError: invalidQuery: No matching signature for operator IN for argument types STRING and {ARRAY<STRING>} at [1:116]>

Google::Cloud::InvalidArgumentError: invalidQuery: No matching signature for operator IN for argument types STRING and {ARRAY<STRING>} at [1:116]
from /usr/local/var/rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/google-cloud-bigquery-0.23.0/lib/google/cloud/bigquery/service.rb:662:in `rescue in execute'

Any insights would be greatly appreciated.

Answer 1

I believe the array is simply an incorrect argument. According to Functions & Operators, the syntax is:

x IN (y, z, ...)

Notice that there is no array.

And replacing the array with a single string works:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new

sql = "SELECT word, SUM(word_count) AS word_count " \
      "FROM `bigquery-public-data.samples.shakespeare`" \
      "WHERE word IN (@words) GROUP BY word"
data = bigquery.query sql, params: { words: 'you' }
#=> [{"word"=>"you", "word_count"=>12527}]

The array argument works with the addition of UNNEST, per the syntax guide linked above:

The UNNEST form treats an array scan like UNNEST in the FROM clause:

x [NOT] IN UNNEST(<array expression>)

This form is often used with ARRAY parameters. For example:

x IN UNNEST(@array_parameter)

So the solution is:

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new

sql = "SELECT word, SUM(word_count) AS word_count " \
      "FROM `bigquery-public-data.samples.shakespeare`" \
      "WHERE word IN UNNEST(@words) GROUP BY word"
data = bigquery.query sql, params: { words: ['me', 'I', 'you'] }
#=> [{"word"=>"I", "word_count"=>21028}, {"word"=>"me", "word_count"=>8030}, {"word"=>"you", "word_count"=>12527}]