How to map column name into query response if there is data present in column?

user_6612592 0
Viewed 122

So lets say I have table like this with unknown number of columns. I would like to know if there is a way to map env struct properties into columns.

ID | env.foo | env.bar | .... | unknown number of env. columns | .... | env.xyz |
1  | prod    | null    | null | null                           | null | null    |
2  | prod    | prod    | null | null                           | null | null    |
3  | null    | null    | null | null                           | null | prod    |

My desired output from this table would be like this

ID | env 
1  | foo 
2  | foo 
2  | bar 
3  | xyz
google-bigquery
1 Answers

Consider below approach

select id, key as env
from your_table t, unnest([struct(to_json_string(env) as kv)]),
unnest(`bqutil.fn.json_extract_keys`(kv)) key with offset 
join unnest(`bqutil.fn.json_extract_values`(kv)) value with offset 
using (offset)
where not value is null

if applied to sample data as in your question

with your_table as (
  select 1 id, struct<foo string, bar string, abc string, xyz string>
    ('prod', null, null, null) as env union all
  select 2, ('prod', 'prod', null, null) as env union all
  select 3, (null, null, null, 'prod') as env 
)

output is

enter image description here

user_52219440
Related