Does anyone know how to alter a data type for a nested column? Whenever I reference the column like address.state or something like that, I get a column not found error. Is there any way to target a nested column with DDL?
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#alter_column_set_data_type_statement
You need to specify the whole structure of the “outer” field. E.g.:
insert into `dataset.test` values (1, (1, 1));
alter table dataset.test alter column lala set data type STRUCT <a FLOAT64, b INT64>```
However, you can't change all data types to any others, it has to be assignable.
E.g., if you try to do
```alter table dataset.test alter column lala set data type STRUCT <a FLOAT64, b STRING>;```
You will get an error `ALTER TABLE ALTER COLUMN SET DATA TYPE requires that the existing column type (STRUCT<a FLOAT64, b INT64>) is assignable to the new type (STRUCT<a FLOAT64, b STRING>)`if you need to change some data type that doesn’t work with just the basic assignment, you will need to overwrite the table by itself with a query where you properly transform the field into a new type. Or change the column name to e.g. column_name_old, create a new one with newly specified data type, update the table by setting this column to transformed value of an old column and drop the old one.
