In BigQuery, How can I turn many columns into a RECORD or Array of Key Value pairs e.g.
Source Table
| id | Name | DOB | Sex |
|---|---|---|---|
| 1 | Fred | 01.01.2001 | M |
Destination Table
| Id | Name | Key | Value |
|---|---|---|---|
| 1 | Fred | DOB | 01.01.2001 |
| Sex | M |
I've tried a few things but cant get there, is there a nice way of doing it?
I've tried a few things but cant get there, is there a nice way of doing it?
Not sure what exactly was /could be an issue here as it is as simple/straightforward as below
select id, Name,
[struct<key string, value string>('DOB', DOB),('Sex', Sex)] info
from `project.dataset.table`
with output
Meantime, usually the issue comes when you don't know those columns names in advance and want to have generic approach - in this case you can use below approach where column names DOB and Sex are not being used
select id, Name,
array(
select as struct
split(replace(kv, '"', ''),':')[offset(0)] key,
split(replace(kv, '"', ''),':')[offset(1)] value,
from unnest(split(trim(to_json_string((select as struct * except (id, name) from unnest([t]))), '{}'))) kv
) info
from `project.dataset.table` t
with exact same result/output
Here you go with a solution:-
WITH `proj.dataset.tbl` AS
(SELECT '1' AS id, 'Fred' AS Name, '2020-12-07' AS DOB, 'M' as Sex
)
SELECT id, Name,
[struct('DOB' as key, cast (DOB as string) as value ),
struct('Sex' as key, Sex as value)
] as key_values
FROM `proj.dataset.tbl`
Output will be as :-
