Search code examples
gogoogle-bigquery

Update struct or nested field in bigquery

Correct way to update a nested field or a struct in a BigQuery table.

SQL statement:

UPDATE myTable
SET Employee.name = 'some string'
WHERE id = 10

Here's my code:

ctx := context.Background()
client, err := bigquery.NewClient(ctx, projectID)
if err != nil {
    return fmt.Errorf("bigquery.NewClient: %v", err)
}
defer client.Close()

q := client.Query("UPDATE myTable SET Employee.name = 'some string' WHERE id = @id  ")
q.Parameters = []bigquery.QueryParameter{
  { 
   Name: "id",
    Value: 10
  },
}

job, err := q.Run(ctx)
if err != nil {
    return err
}

status, err := job.Wait(ctx)
if err != nil {
    return err
}

if err := status.Err(); err != nil {
    return err
}

My table: enter image description here

How do I update this struct or nested field?

Solution

  • Unfortunately BigQuery does not support updating separate fields of a query or a nested field. The following syntax is not supported:

    UPDATE myTable
SET Employee.name = 'some string'
WHERE id = 10

    We can update structs only as a whole:

    UPDATE myTable
SET Employee = STRUCT('aaa', 'bbb')
WHERE id = 10