How to INSERT from one nested table into another nested table in BigQuery

While copying one nested BigQuery table into another nested table and am running into the following error: "Syntax error: Expected ")" or "," but got "."

Solution:

In the below table, the column addresses is an ARRAY of STRUCT

INSERT INTO `<GCP_PROJECT_NAME>.Test_Tables.Nested_Person_Table2` \n    (id,\n    first_name,\n    last_name,\n    dob,\n    addresses)\nSELECT\n  id,\n  first_name,\n  last_name,\n  dob,\n  ARRAY_AGG(STRUCT(a1.status,\n      a1.address,\n      a1.city,\n      a1.state,\n      a1.zip,\n      a1.numberOfYears)) AS addresses\nFROM\n  `<GCP_PROJECT_NAME>.Test_Tables.Nested_Person_Table`,\n  UNNEST(addresses) AS a1\nGROUP BY\n  id,\n  first_name,\n  last_name,\n  dob\n

Mike-Barn

posted on