CODEWITHSUNDEEP
arraysgoogle-bigqueryrecord
MilleniumDK
MilleniumDK

Reputation: 31

BigQuery Convert Columns to RECORD

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?

Upvotes: 3

Views: 467

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172954

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

enter image description here

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

enter image description here

Upvotes: 1

Vibhor Gupta
Vibhor Gupta

Reputation: 699

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 :-

enter image description here

Upvotes: 0

Related Questions