BigQuery - Using INSERT INTO to copy data from one nested table into another nested table

Question

Helping a customer out. I'm trying to copy one nested BigQuery table into another nested table and am running into the following error: "Syntax error: Expected ")" or "," but got ".""

Query:

  INSERT INTO `<GCP_PROJECT_NAME>.Test_Tables.Nested_Person_Table2` (id,
    first_name,
    last_name,
    dob,
    address.status,
    address.address,
    address.city,
    address.state,
    address.zip,
    address.numberOfYears)
SELECT
  id,
  first_name,
  last_name,
  dob,
  address.status,
  address.address,
  address.city,
  address.state,
  address.zip,
  address.numberOfYears
FROM
  `<GCP_PROJECT_NAME>.Test_Tables.Nested_Person_Table`

Nick Orlove · Accepted Answer · 2019-06-13 18:02:41Z

42

Answer below. Hope this helps someone else out too!

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

answered Jun 13, 2019 at 18:02

Nick Orlove

5591 gold badge4 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

BigQuery - Using INSERT INTO to copy data from one nested table into another nested table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related