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

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

CodePudding user response：

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;

CodePudding user response：

Try this

create table `project_id.dataset.new_table_name` as 
SELECT 
      [STRUCT (P.NAME AS NAME)] AS PERSON

from `project_id.dataset.table_name`,UNNEST(PERSON) AS P;