使用 Bigquery 删除具有"REPEATED"模式的嵌套列

Question

使用Bigquery，我试图删除下面这样的嵌套模式，但无法做到。有人能告诉我吗。如何做到这一点？

表格：

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

代码：

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`;

预期输出：

FiledName          Type       Mode
Person            RECORD   REPEATED
Person.Name       STRING   NULLABLE

Answer 1

考虑以下方法

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;

Answer 2

试试这个

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;