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
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;
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
>
>
>
>
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]
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.