How to create new table with nested schema entirely in BigQuery

Question

I've got a nested table A in BigQuery with a schema as follows:

    {
    "name": "page_event",
    "mode": "repeated",
    "type": "RECORD",
    "fields": [
        {
            "name": "id",
            "type": "STRING"
        }
    ]
    }

I would like to enrich table A with data from other table and save result as a new nested table. Let's say I would like to add "description" field to table A (creating table B), so my schema will be as follows:

    {
    "name": "page_event",
    "mode": "repeated",
    "type": "RECORD",
    "fields": [
        {
            "name": "id",
            "type": "STRING"
        },
        {
            "name": "description",
            "type": "STRING"
        }
    ]
    }

How do I do this in BigQuery? It seems, that there are no functions for creating nested structures in BigQuery SQL (except NEST functions, which produces a list - but this function doesn't seem to work, failing with Unexpected error)

The only way of doing this I can think of, is to:

• use string concatenation functions to produce table B with single field called "json" with content being enriched data from A, converted to json string
• export B to GCS as set of files F
• load F as table C

Is there an easier way to do it?

Answer 1

To enrich schema of existing table one can use tables patch API
https://cloud.google.com/bigquery/docs/reference/v2/tables/patch

Request will look like below

PATCH https://www.googleapis.com/bigquery/v2/projects/{project_id}/datasets/{dataset_id}/tables/{table_id}?key={YOUR_API_KEY}

{
 "schema": {
  "fields": [
   {
    "name": "page_event",
    "mode": "repeated",
    "type": "RECORD",
    "fields": [
     {
      "name": "id",
      "type": "STRING"
     },
     {
      "name": "description",
      "type": "STRING"
     }
    ]
   }
  ]
 }
}

Before Patch

After Patch