BigQuery, unpivot table

I have a table as :

Province,Province_id,red_lexus,blue_lexus,red_honda,grey_honda
ON,1,50,20,20,40
BC,2,100,20,40,66
AB,3,45,34,20,10

now i need to unpivot this table to obtain something like this:

Province,Province_id,CarCategory,CarCategoryCount
ON,1,red_lexus,50
ON,1,blue_lexus,20
ON,1,red_honda,20
ON,1,grey_honda,20
BC,2,red_lexus,100
BC,2,blue_lexus,20
...

The column name for car categories (i.e. red_lexus, blue_lexus,...) are not bound and the only things that I know is that i have to read all the column after ordinal_position 2.

how can I achieve that in Google bigQuery?

Thanks,

Solution

Consider below approach

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 Province,Province_id, CarCategory, CarCategoryCount
from your_table t, 
unnest(extract_keys(to_json_string(t))) CarCategory with offset
join unnest(extract_values(to_json_string(t))) CarCategoryCount with offset
using(offset)
where offset > 1

if applied to sample data in your question - output is