CODEWITHSUNDEEP
sqlgoogle-bigquery
Kartikey Mullick
Kartikey Mullick

Reputation: 71

Filtering rows in bigquery based on user parameters

I have a following sample table:

Info County State
info1 Sonoma California
info2 Fresno California
info3 Lake California
info5 Lake Florida

I need to select say, Sonoma county from California and Lake county from Florida. I need to pass the needed counties and the needed states as list parameters so that they could be given as arguments to a script.

SELECT
    ST_UNION_AGG(Info) as counties_info
  FROM
    `project.dataset.table`
  WHERE
    County IN UNNEST(@counties) AND
    State IN UNNEST(@states)

This query will also give me the Lake county in California, how do I modify the script while still using the needed list parameters [@counties and @states]?

NOTE: I cannot check for individual states as the @states parameters can have any state in it.

Upvotes: 1

Views: 578

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173210

You can simply use

select ...
from `project.dataset.table`
where (State, County) in unnest(@state_county_pairs)

above assumes that your @state_county_pairs parameter is an array of sate, county pairs - like in below script

declare state_county_pairs array<struct<State string, Country string>> ;
set state_county_pairs =  [
  struct('California' as State, 'Sonoma' as County),
  struct('Florida', 'Lake')
];

select *
from `project.dataset.table`
where (State, County) in unnest(state_county_pairs)

with output (if applied to sample data in your question)

enter image description here

Obviously, then you can do whatever aggregation or other logic you need
I am not reusing your ST_UNION_AGG(Info) as counties_info because it makes no sense having in mind presented values of Info field, but I hope this was just for simplification purposes, so I am using just select * here

Meantime, below is example using real data with geo data type which applicable to use with ST_ functions

select st_union_agg(county_geom) as counties_geo
from `bigquery-public-data.utility_us.us_county_area`
join `bigquery-public-data.utility_us.us_states_area`
using (state_fips_code)
where (state_name, county_name) in unnest(@var_state_county_pairs)

with output that combines those two counties geo info and is visualized as below

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

I would recommend using structs for the parameters:

SELECT ST_UNION_AGG(t.Info) as counties_info
FROM `project.dataset.table` t
WHERE EXISTS (SELECT 1
              FROM UNNEST(@county_states) cs
              WHERE cs.county = t.county AND cs.state = t.state
             );

Upvotes: 3

Related Questions