How Create Nested table from an existing table using select statement in BigQuery

0
votes

I have to create a nested table with repeated columns in BigQuery by selecting them from an input table.

I have sample input data looks like this,

v1  ITPK8   E97
v1  PNLGVO  152
v2  SCDVOL  A55
v1  DA      38
v3  RES35   848
v2  ITPK4   E97
v3  SSFAP   622

And the expected should be

v1  ITPK8   E97
    PNLGVO  152
    DA      38
v2  SCDVOL  A55
    ITPK4   E97
v3  RES35   848
    SSFAP   622

Please let me know the generic BigQuery to achieve this? I have a lot of data present in the input table hence I require Generic query please.

I have tried with below query but no luck. Output data is not generated as expected.

create table `project.db.table_tgt` as 
select v_num, [STRUCT<obj1 STRING, obj2 STRING> (obj1, obj2)] as object 
from `project.db.table_src` order by v_num
1
google-cloud-platformgoogle-bigquery

1 Answers

2
votes

Try doing:

create table `project.db.table_tgt` as 
select v_num, array_agg(STRUCT(obj1, obj2)) as object 
from `project.db.table_src` group by v_num order by v_num