Filtering rows in bigquery based on user parameters

Question

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.

Gordon Linoff · Accepted Answer · 2021-04-07 16:12:46Z

3

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
             );

answered Apr 7, 2021 at 16:12

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mikhail Berlyant · Accepted Answer · 2021-04-10 17:34:51Z

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)

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

Collectives™ on Stack Overflow

Filtering rows in bigquery based on user parameters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related