Bigquery table nested multi-valued columns giving error when querying

Question

I am trying to load data from one table with nested multi-valued column to another table with the same schema, but with one or fields less.

Table-1 Schema:

    Person                  RECORD   REPEATED   ;
    Person.Address          RECORD   REPEATED   ;
    Person.Address.phone    INTEGER  NULLABLE   ;
    Person.Address.zip      INTEGER  NULLABLE   ;
    Person.flag             INTEGER             ;
    Wt                      INTEGER             ;

Table-2 Schema:

    Person                  RECORD   REPEATED   ;
    Person.Address          RECORD   REPEATED   ; 
    Person.Address.phone    INTEGER  NULLABLE   ;
    Person.flag             INTEGER             ;
    Wt                      INTEGER             ;

Please note that the only difference between these two tables is that Table-2 does not have the "zip" field.

I am trying to load Table-2 from Table-1 using the below query:

    INSERT INTO TABLE-2
    SELECT
    ARRAY (SELECT AS STRUCT (ARRAY (SELECT AS STRUCT (SELECT PA.phone from unnest (P.Address) as 
    PA)), P.flag) from unnest(Table-1.Person) as P), s.Wt
    from Table-1

This always throws the below error: "Query column 1 has type ARRAY>, INT64>>> which cannot be inserted into column Person, which has type ARRAY>, flag INT64>> at [2:1]"

If you look at the query, it exactly matches with that of the target column. Any idea on what is going wrong?

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2020-05-21T05:22:14

Below is for BigQuery Standard SQL

INSERT INTO `project.dataset.table2`
SELECT * REPLACE(
  ARRAY(
    SELECT AS STRUCT * REPLACE(
      ARRAY(SELECT AS STRUCT * EXCEPT(zip) FROM p.Address) 
      AS Address) 
    FROM t.Person p
  ) AS Person
  )
FROM `project.dataset.table1` t

Above produces exactly same schema as in table1 with exception of zip field by just "extracting" that field from Address record. Everything else stays intact no matter how many more other fields you have there

Bigquery table nested multi-valued columns giving error when querying

1 Answers