I run into a problem when trying to migrate data between BigQuery tables, I have an old table that has an attribute nested as Float64, I want to migrate the data from this old table, to another one, in which that attribute was declared as Int64, but when I put the query that extracts all the attributes and do the data type conversion, I get an error that I am trying to insert a STRING, instead of the structured object

Query

INSERT INTO `MongoDB.MS-DB_MS-COLLECTION_temp`
  SELECT
    t.* EXCEPT(paymentschemavariablesrecords),
  ARRAY(
    SELECT AS STRUCT
      createdat_date,
      creatorid,
      creatorname,
      creatorroles,
      creatorsurname,
      ARRAY(
          SELECT AS STRUCT
              code,
              id_oid,
              kind,
              name,
              CAST(ROUND(value) AS INTEGER) AS value
          FROM UNNEST(newvalue)
      ) AS newvalue,
      ARRAY(
          SELECT AS STRUCT
              code,
              id_oid,
              kind,
              name,
              CAST(ROUND(value) AS INTEGER) AS value
          FROM UNNEST(oldvalue)
      ) AS oldvalue
    FROM UNNEST(t.paymentschemavariablesrecords)
    ) AS paymentschemavariablesrecords
FROM `MongoDB.MS-DB_MS-COLLECTION` t;

Target BigQuery Table Schema on this attribute

paymentschemavariablesrecords ARRAY<
                                STRUCT<
                                  createdat_date TIMESTAMP,
                                  creatorid STRING,
                                  creatorname STRING,
                                  creatorroles ARRAY<STRING>,
                                  creatorsurname STRING,
                                  newvalue ARRAY<
                                    STRUCT<
                                      code STRING,
                                      id_oid STRING,
                                      kind STRING,
                                      name STRING,
                                      value INT64
                                    >
                                  >,
                                  oldvalue ARRAY<
                                    STRUCT<
                                      code STRING,
                                      id_oid STRING,
                                      kind STRING,
                                      name STRING,
                                      value INT64
                                    >
                                  >
                                >
                              >

Error

Query column 125 has type STRING which cannot be inserted into column paymentschemavariablesrecords, which has type ARRAY<STRUCT<createdat_date TIMESTAMP, creatorid STRING, creatorname STRING, ...>> at [2:3]

Other queries attempted

INSERT INTO `MongoDB.MS-DB_MS-COLLECTION_temp`
  SELECT
    t.* EXCEPT(paymentschemavariablesrecords),
    CAST(ARRAY(
      SELECT AS STRUCT
        CAST(createdat_date AS TIMESTAMP) AS createdat_date,
        CAST(creatorid AS STRING) AS creatorid,
        CAST(creatorname AS STRING) AS creatorname,
        CAST(creatorroles AS ARRAY<STRING>) AS creatorroles,
        CAST(creatorsurname AS STRING) AS creatorsurname,
        ARRAY(
          SELECT AS STRUCT
            CAST(code AS STRING) AS code,
            CAST(id_oid AS STRING) AS id_oid,
            CAST(kind AS STRING) AS kind,
            CAST(name AS STRING) AS name,
            CAST(ROUND(value) AS INT64) AS value
          FROM UNNEST(newvalue)
        ) AS newvalue,
        ARRAY(
          SELECT AS STRUCT
            CAST(code AS STRING) AS code,
            CAST(id_oid AS STRING) AS id_oid,
            CAST(kind AS STRING) AS kind,
            CAST(name AS STRING) AS name,
            CAST(ROUND(value) AS INT64) AS value
          FROM UNNEST(oldvalue)
        ) AS oldvalue
      FROM UNNEST(t.paymentschemavariablesrecords)
    ) AS ARRAY<
          STRUCT<
            cratedat_date TIMESTAMP,
            creatorid STRING,
            creatorname STRING,
            creatorroles ARRAY<STRING>,
            creatorsurname STRING,
            newvalue ARRAY<
              STRUCT<
                code STRING,
                id_oid STRING,
                kind STRING,
                name STRING,
                value INT64
              >
            >,
            oldvalue ARRAY<
              STRUCT<
                code STRING,
                id_oid STRING,
                kind STRING,
                name STRING,
                value INT64
              >
            >
          >
        >
      ) as paymentschemavariablesrecords
  FROM `MongoDB.MS-DB_MS-COLLECTION` t;

I want to manage to migrate data from one table to another, modifying the data type of this nested attribute

The error is caused by any other field is attempted to be inserted in the position of paymentschemavariablesrecords.

When you perform an insert into a table using a SELECT statement, the order of the columns selected must match the table's schema. If you use EXCEPT in your query, the order of the columns is altered. Therefore, all columns must be selected, and you need to place the new code for paymentschemavariablesrecords in the corresponding position within the schema.