Union All Parameterised Queries

I have a certain query which is working fine. The problem is that a part of that query is a string that needs to be read from a file. Query for each string produces 6 outputs. I ne

Solution 1:

Please look at below example using public data (you can run the query as well)

#standardSQL
SELECT * 
FROM `bigquery-public-data.baseball.schedules`
WHERE (year, duration_minutes) IN UNNEST([(2016, 187), (2016, 165), (2016, 189)])

The key here is for you to provide an array of value that you want to filter the table with, and use IN UNNEST(array_of_values) to do the job, ideally like below:

query = """
    SELECT pet_id, age, name
    FROM `myproject.mydataset.mytable`
    WHERE (name, species) IN UNNEST(@filter_array);
"""

It is a bit unfortunate that BigQuery Python API doesn't let you specify array< struct<string, int64> > as query parameter. So you may have to do:

query = """
    SELECT pet_id, age, name
    FROM `myproject.mydataset.mytable`
    WHERE concat(name, "_", species) IN UNNEST(@filter_array);
"""array_of_pre_concatenated_name_and_species = ['Max_Dog', 'Alfred_Cat']
query_params = [
    bigquery.ArrayQueryParameter('filter_array', 'STRING', array_of_pre_concatenated_name_and_species),
]