Using Bigquery remove the nested columns which has "REPEATED" mode

December 30, 2021

Using Bigquery I was trying to remove nested schema like below, but couldn’t able to do so. Can anyone please let me know. How to achieve that?

Table:

FiledName          Type       Mode
Person            RECORD   REPEATED
Person.Name       STRING   NULLABLE
Person.Add        RECORD   NULLABLE
Person.Add.line   STRING   NULLABLE

Code:

create table `project_id.dataset.new_table_name` as 
select * replace(
   (select as ARRAY(struct person.* except(add))) as person
)
from `project_id.dataset.table_name`;

Expected Output:

FiledName          Type       Mode
Person            RECORD   REPEATED
Person.Name       STRING   NULLABLE

>Solution :

Consider below approach

create table `project_id.dataset.new_table_name` as 
select * replace(
    array(select as struct person.* except(add) from t.person) as person
  )
from `project_id.dataset.table_name` t;