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` 
    (id,
    first_name,
    last_name,
    dob,
    addresses)
SELECT
  id,
  first_name,
  last_name,
  dob,
  ARRAY_AGG(STRUCT(a1.status,
      a1.address,
      a1.city,
      a1.state,
      a1.zip,
      a1.numberOfYears)) AS addresses
FROM
  `<GCP_PROJECT_NAME>.Test_Tables.Nested_Person_Table`,
  UNNEST(addresses) AS a1
GROUP BY
  id,
  first_name,
  last_name,
  dob

Mike-Barn

posted on 01 Feb 21