How do I select columns based on a string pattern in BigQuery

August 27, 2022

I have a table in BigQuery with hundreds of columns, and it just happens that I want to select all of them except for those that begin with an underscore. I know how to do a query to select the columns beginning with an underscore using the INFORAMTION_SCHEMA.COLUMNS table, but I can’t figure out how I would use this query to select the columns I want. I know BigQuery has EXCEPT but I want to avoid writing out each column that begins with an underscore, and I can’t seem to pass to it a subquery or even something like a._*.

>Solution :

Consider below approach

execute immediate (select '''
   select * except(''' || string_agg(col) || ''') from your_table
'''
from (
  select col
  from (select * from your_table limit 1) t,
  unnest([struct(translate(to_json_string(t), '{}"', '') as kvs)]),
  unnest(split(kvs)) kv, 
  unnest([struct(split(kv, ':')[offset(0)] as col)])
  where starts_with(col, '_')
));

if apply to table like below