Query table with unpredictable number of columns by BigQuery

user_18044427 0
Viewed 113

Let's say, I have a table from log, it looks like this: Log Table

I need a result table like: Result table

But the problem is my dataset is not only 7, but maybe 24, 100 value columns. My query with 7 value columns is:

select 
*
from My_Dataset
unpivot 
(status for value in (value_1, value_2, value_3, value_4, value_5, value_6, value_7))```

But is there anyway to automatic this process for value_n?
Thank you.
google-bigquery
2 Answers

Consider below approach

select id, arr[offset(1)] as value
from your_table t,
unnest(split(translate(to_json_string(t), '{}"', ''))) kv, 
unnest([struct(split(kv, ':') as arr)])
where starts_with(arr[offset(0)], 'value_')

if applied to sample data in your question (i used only three value_N columns but it works for any!)

enter image description here

Another option (maybe less verbose and simpler to swallow)

select id, val
from your_table t, unnest([to_json_string(t)]) json,
unnest(`bqutil.fn.json_extract_keys`(json)) col with offset 
join unnest(`bqutil.fn.json_extract_values`(json)) val with offset 
using(offset)
where starts_with(col, 'value_')

obviously with same output as above first option

user_52219440

It's possible via SQL scripting.

You have to get a column list of your table first and save it in variable. Then call dynamic query with EXECUTE IMMEDIATE

DECLARE field_list STRING;

SET field_list = ((
    SELECT STRING_AGG(column_name) FROM `my_project_id`.my_dataset.INFORMATION_SCHEMA.COLUMNS
    WHERE column_name LIKE "value_%" AND table_name = 'my_table'
));

EXECUTE IMMEDIATE "SELECT id, SPLIT(value, '_')[OFFSET(1)] value FROM  my_dataset.my_table UNPIVOT (status FOR value IN ("||field_list||"))"
user_124726440
Related