We are generating a dynamic query using the script below :
x
execute immediate (select
''' select event_id, value, ''' || string_agg('''
(select value from b.key_value where key = "''' || key_name || '''") as ''' || key_name , ''', ''')
|| '''
from (
select event_id, value,
array(
select as struct
json_extract_scalar(kv, '$.key') key,
json_extract_scalar(kv, '$.value') value
from a.kvs kv
) key_value
from `project.dataset.table`,
unnest([struct(json_extract_array(dimensions, '$.key_value') as kvs)]) a
) b
'''
from (
select distinct json_extract_scalar(kv, '$.key') key_name
from `project.dataset.table`,
unnest(json_extract_array(dimensions, '$.key_value')) as kv
)
)
We want to create a view out of this is there any way in BQ by which we can create a view from this, so as user we can directly query our view.
Advertisement
Answer
Should be as simple as just adding create or replace view project.dataset.myview as
to original query
execute immediate (select
''' create or replace view `project.dataset.myview` as select event_id, value, ''' || string_agg('''
(select value from b.key_value where key = "''' || key_name || '''") as ''' || key_name , ''', ''')
|| '''
from (
select event_id, value,
array(
select as struct
json_extract_scalar(kv, '$.key') key,
json_extract_scalar(kv, '$.value') value
from a.kvs kv
) key_value
from `project.dataset.table`,
unnest([struct(json_extract_array(dimensions, '$.key_value') as kvs)]) a
) b
'''
from (
select distinct json_extract_scalar(kv, '$.key') key_name
from `project.dataset.table`,
unnest(json_extract_array(dimensions, '$.key_value')) as kv
)
)