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

user_2370793 0
Viewed 916

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"
            }
        ]
    }
]
google-bigquery nested-lists google-cloud-platform
1 Answers

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
user_52219440
Related