How to delete a column in BigQuery that is part of a nested column

Question

I want to delete a column in a BigQuery table that is part of a record or nested column. I've found this command in their documentation. Unfortunately, this command is not available for nested columns inside existing RECORD fields. Is there any workaround for this?

For example, if I had this schema I want to remove the address2 field inside the address field. So from this:

[
    {
        "name": "name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "addresses",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
            {
                "name": "address1",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "address2",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "country",
                "type": "STRING",
                "mode": "NULLABLE"
            }
        ]
    }
]

to this:

[
    {
        "name": "name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "addresses",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
            {
                "name": "address1",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "country",
                "type": "STRING",
                "mode": "NULLABLE"
            }
        ]
    }
]

Answer 1

Use below

select * replace(
  array(select as struct * except(address2) from t.addresses) 
  as addresses)
from `project.dataset.table` t       

if you want permanently remove that field - use create or replace table as in below example

create or replace table `project.dataset.new_table` as 
select * replace(
  array(select as struct * except(address2) from t.addresses) 
  as addresses)
from `project.dataset.table` t