问如何在BigQuery表中的450个列中找到"-32767“值？

EN

create temp function  extract_keys(input string) returns array<string> language js as """
  return Object.keys(JSON.parse(input));
  """;
create temp function  extract_values(input string) returns array<string> language js as """
  return Object.values(JSON.parse(input));
  """;
select col, count(*) cnt
from your_table t,
unnest([struct(to_json_string(t) as json)]),
unnest(extract_keys(json)) col with offset
join unnest(extract_values(json)) val with offset
using(offset)
where val = '-32767'
group by col    

select col, count(*) cnt
from your_table t,
unnest([struct(to_json_string((select as struct col1, col3 from unnest([t]))) as json)]),
unnest(extract_keys(json)) col with offset
join unnest(extract_values(json)) val with offset
using(offset)
where val = '-32767'
group by col