Google Big Query - list of values in WHERE clause-CodePudding

I am trying to query geometry data from google big query public tables and trying to figure out how to pass a list of values in WHERE clause / filter the query. Note that I am writing the query in python.

Here's my query:

from google.cloud import bigquery

# Obtain geometries from Google Big Query 

metros = ['Los Angeles-Long Beach-Anaheim, CA','Phoenix-Mesa-Chandler, AZ']

sql = """
  SELECT *
  FROM `bigquery-public-data.geo_us_boundaries.cbsa`
  WHERE name IN UNNEST(metros)
"""

df = client.query(sql).to_dataframe()

I get :

BadRequest: 400 Unrecognized name: metros at [4:24]

CodePudding user response：

you need to pass the array like so :

from google.cloud import bigquery

# Obtain geometries from Google Big Query 

metros = ['Los Angeles-Long Beach-Anaheim, CA','Phoenix-Mesa-Chandler, AZ']

sql = """
  SELECT *
  FROM `bigquery-public-data.geo_us_boundaries.cbsa`
  WHERE name IN UNNEST(%s)""" %(metros)


df = client.query(sql).to_dataframe()